Could not get download transportion data using DuckDB in ubuntu

[ajayvsv]

I have accessed data of Places and Buildings. But, I could not accessed transportation data for the same location.

Getting this error:

Error: Not implemented Error: Unsupported type for OGR: STRUCT(property VARCHAR, dataset VARCHAR, recordId VARCHAR, confidence DOUBLE)[]

here is my query :

COPY ( SELECT type, version, level, subtype, road, connectors, id, sources, geometry FROM read_parquet('s3://overturemaps-us-west-2/release/2023-11-14-alpha.0/theme=transportation/type=/', hive_partitioning=1)
WHERE bbox.minx > 76.38 AND bbox.maxx < 78.64 AND bbox.miny > 13.30 AND bbox.maxy < 15.60)
TO 'transport.geojsonseq' WITH (FORMAT GDAL, DRIVER 'GeoJSONSeq');

[mtravis]

The source field is a struct and gdal won't load that , hence the error.

Try casting sources as json using JSON(sources) as sources

[ajayvsv]

Thank you Matt Travis.
I have tried the same, but failed.
Error: Not implemented Error: Unsupported field type

To get the count of features, I am getting 432506 features using this query
COPY (SELECT count(id) FROM read_parquet('s3://overturemaps-us-west-2/release/2023-11-14-alpha.0/theme=transportation/type=/', hive_partitioning=1) WHERE bbox.minx > 76.38 AND bbox.maxx < 78.64 AND bbox.miny > 13.30 AND bbox.maxy < 15.60 ) TO 'transport.geojsonseq' WITH (FORMAT GDAL, DRIVER 'GeoJSONSeq');

[jwass]

Hi @ajayvsv -
@mtravis is right you need to cast the array columns to JSON - but it looks like the struct columns are fine. For your query that's both sources and connectors. But more importantly, you'll need to load up the geometry with ST_GEOMFROMWKB(geometry). This worked for me (Note also the change in the read_parquet() path for this example).

COPY (
        SELECT
                type,
                version,
                level,
                subtype,
                road,
                CAST(connectors AS JSON) AS connectors,
                id,
                CAST(sources AS JSON) AS sources,
                ST_GEOMFROMWKB(geometry) AS geometry
        FROM read_parquet('s3://overturemaps-us-west-2/release/2023-11-14-alpha.0/theme=transportation/type=segment/*.parquet', hive_partitioning=1)
        LIMIT 5
)
TO 'transport.geojsonseq' WITH (FORMAT GDAL, DRIVER 'GeoJSONSeq');

$ cat transport.geojsonseq
{ "type": "Feature", "properties": { "type": "segment", "version": 0, "level": null, "subType": "road", "road": { "class": "tertiary", "surface": "gravel", "flags": [ { "value": [ "isBridge" ], "at": [ 0.341436987, 0.348988328 ] }, { "value": [ "isBridge" ], "at": [ 0.52067618, 0.52606418 ] } ], "roadNames": { "common": [ { "language": "local", "value": "Waitangi Tuku Road" } ] } }, "connectors": [ "8fbb364d1274645-1669BCCB44D15F7B", "8fbb364c0c6b302-13385E249DB57755" ], "id": "85bb364ffffffff-17E79D76883C3C76", "sources": [ { "property": "", "dataset": "OpenStreetMap", "recordId": null, "confidence": null } ] }, "geometry": { "type": "LineString", "coordinates": [ [ -176.5672268, -43.9544686 ], [ -176.5689302, -43.9555656 ], [ -176.5711006, -43.956908 ], [ -176.5741925, -43.9587708 ], [ -176.5765961, -43.9622014 ], [ -176.5769109, -43.9627203 ], [ -176.5771067, -43.963068 ], [ -176.5776099, -43.9636121 ], [ -176.5787029, -43.965401 ], [ -176.5798074, -43.9665115 ], [ -176.5800708, -43.966755 ], [ -176.5812143, -43.9675002 ], [ -176.5845958, -43.969581 ], [ -176.5849754, -43.9698278 ], [ -176.5854393, -43.9701294 ], [ -176.5874994, -43.9714688 ], [ -176.5881884, -43.9719353 ], [ -176.5905955, -43.973765 ], [ -176.5936714, -43.9760454 ], [ -176.5942914, -43.9765039 ], [ -176.5948022, -43.9771257 ], [ -176.5953248, -43.9774453 ], [ -176.5956876, -43.977632 ], [ -176.5961946, -43.9778928 ], [ -176.5975436, -43.9784809 ], [ -176.5988253, -43.978963 ], [ -176.5991659, -43.9791563 ], [ -176.6014674, -43.9811726 ], [ -176.6036931, -43.9837271 ], [ -176.6044692, -43.9852866 ], [ -176.6046515, -43.9854999 ], [ -176.6050156, -43.9855927 ], [ -176.6053543, -43.9855301 ], [ -176.6059959, -43.9852671 ] ] } }
{ "type": "Feature", "properties": { "type": "segment", "version": 0, "level": null, "subType": "road", "road": { "class": "driveway" }, "connectors": [ "8fbb364d3a6670e-1379FFF3FC9AEF95", "8fbb364d3a606c8-1468EEA3217298FF" ], "id": "8abb364d3a67fff-13F0FF4B80AF0856", "sources": [ { "property": "", "dataset": "OpenStreetMap", "recordId": null, "confidence": null } ] }, "geometry": { "type": "LineString", "coordinates": [ [ -176.5462721, -43.9523941 ], [ -176.546811, -43.9520126 ] ] } }
{ "type": "Feature", "properties": { "type": "segment", "version": 0, "level": null, "subType": "road", "road": { "class": "unknown" }, "connectors": [ "8fd44db2d0e00f5-16BBD12350BADF01", "8fd44db290636e1-11B567505BAE8737" ], "id": "86d44db2fffffff-10614ECBAD9BD89B", "sources": [ { "property": "", "dataset": "OpenStreetMap", "recordId": null, "confidence": null } ] }, "geometry": { "type": "LineString", "coordinates": [ [ -176.5326795, -43.9281236 ], [ -176.5327102, -43.9280348 ], [ -176.5326862, -43.9279436 ], [ -176.5327134, -43.9277686 ], [ -176.5327008, -43.9276154 ], [ -176.5324471, -43.9272807 ], [ -176.5322935, -43.9270194 ], [ -176.5320888, -43.926916 ], [ -176.5319933, -43.9267608 ], [ -176.5320306, -43.9265365 ], [ -176.5321839, -43.9259868 ], [ -176.5323065, -43.9257649 ], [ -176.5323127, -43.9254716 ], [ -176.5322789, -43.9251931 ], [ -176.5323163, -43.9249811 ], [ -176.532282, -43.9247248 ], [ -176.5324046, -43.9243649 ], [ -176.5324761, -43.9242465 ], [ -176.5325347, -43.9239546 ], [ -176.5324876, -43.9237088 ], [ -176.5324586, -43.9234381 ], [ -176.5324381, -43.923236 ], [ -176.5323288, -43.9229427 ], [ -176.5322864, -43.922497 ], [ -176.53226, -43.9221935 ], [ -176.5321986, -43.9219595 ], [ -176.5322422, -43.9217572 ], [ -176.5323295, -43.92146 ], [ -176.5324518, -43.921207 ], [ -176.5325217, -43.9209351 ], [ -176.5325916, -43.9208213 ], [ -176.5326964, -43.9205746 ], [ -176.5328625, -43.9201573 ], [ -176.5329498, -43.9200118 ], [ -176.5330722, -43.9198158 ], [ -176.5331508, -43.919645 ], [ -176.5331507, -43.9194616 ], [ -176.5332469, -43.9192656 ], [ -176.5332992, -43.9190316 ], [ -176.5332991, -43.9187913 ], [ -176.5333339, -43.9184562 ], [ -176.5332988, -43.9182982 ], [ -176.5332899, -43.9179567 ], [ -176.5333598, -43.9178302 ], [ -176.5333859, -43.9176721 ], [ -176.5334121, -43.9175203 ], [ -176.5335169, -43.9173116 ], [ -176.5335256, -43.9171346 ], [ -176.533478, -43.9168323 ], [ -176.5335164, -43.9163884 ], [ -176.5335184, -43.9160855 ], [ -176.533551, -43.9157878 ], [ -176.5336069, -43.9156517 ], [ -176.5336068, -43.9154471 ], [ -176.5337669, -43.9152006 ], [ -176.5337565, -43.9149442 ], [ -176.5337867, -43.9148519 ], [ -176.5339949, -43.9145227 ], [ -176.5340084, -43.9143846 ], [ -176.5340312, -43.9141816 ], [ -176.5340923, -43.9139222 ], [ -176.5341513, -43.9137241 ], [ -176.5341648, -43.9136132 ], [ -176.5341648, -43.9134308 ], [ -176.5341182, -43.9132836 ], [ -176.5341618, -43.9131698 ], [ -176.5343279, -43.9128472 ], [ -176.5345026, -43.9124235 ], [ -176.5346107, -43.9121268 ], [ -176.5346334, -43.9118544 ], [ -176.5347558, -43.9116457 ], [ -176.5348255, -43.9113548 ], [ -176.5349217, -43.9111145 ], [ -176.5350438, -43.9105581 ], [ -176.5351922, -43.9102166 ], [ -176.5353319, -43.9098245 ], [ -176.5353842, -43.9095526 ], [ -176.5355765, -43.9093628 ], [ -176.5357251, -43.9091857 ], [ -176.5358124, -43.9088569 ], [ -176.5360571, -43.9084521 ], [ -176.5361881, -43.9082623 ], [ -176.5362038, -43.9080866 ], [ -176.5363776, -43.907978 ], [ -176.5365377, -43.9077501 ], [ -176.5366425, -43.9075983 ], [ -176.5367036, -43.9074023 ], [ -176.5367035, -43.9072442 ] ] } }
{ "type": "Feature", "properties": { "type": "segment", "version": 0, "level": null, "subType": "road", "road": { "class": "driveway" }, "connectors": [ "8fd44db2d781599-10ABE386A99FEF59", "8fd44db2d79c555-11B5A5BF45014B2B" ], "id": "89d44db2d7bffff-1125A4A07595873D", "sources": [ { "property": "", "dataset": "OpenStreetMap", "recordId": null, "confidence": null } ] }, "geometry": { "type": "LineString", "coordinates": [ [ -176.5251478, -43.9240532 ], [ -176.5248603, -43.9239129 ], [ -176.524238, -43.9236792 ] ] } }
{ "type": "Feature", "properties": { "type": "segment", "version": 0, "level": null, "subType": "road", "road": { "class": "track" }, "connectors": [ "8fd44db514ac80e-13B6FDDE57A02E2E", "8fd44db5302d4d8-16A337C6389AA519" ], "id": "86d44db57ffffff-1322FB56BC489C1F", "sources": [ { "property": "", "dataset": "OpenStreetMap", "recordId": null, "confidence": null } ] }, "geometry": { "type": "LineString", "coordinates": [ [ -176.5471259, -43.8081687 ], [ -176.5470163, -43.8078865 ], [ -176.546888, -43.8078335 ], [ -176.5466633, -43.8079764 ], [ -176.5464982, -43.8080438 ], [ -176.5463546, -43.8081167 ], [ -176.546096, -43.8082729 ], [ -176.5458948, -43.8083459 ], [ -176.5456362, -43.8083563 ], [ -176.545363, -43.808346 ], [ -176.545018, -43.8081693 ], [ -176.5448742, -43.8080236 ], [ -176.5445578, -43.8078884 ], [ -176.5442703, -43.8077844 ], [ -176.5439972, -43.8076804 ], [ -176.5436664, -43.8074516 ], [ -176.5433643, -43.8071498 ], [ -176.5431773, -43.8069209 ], [ -176.5429616, -43.8067024 ], [ -176.5428322, -43.8065672 ], [ -176.5425877, -43.8063904 ], [ -176.5423002, -43.8062967 ], [ -176.5421564, -43.8061407 ], [ -176.5420701, -43.8059846 ], [ -176.5419693, -43.8058493 ], [ -176.5417967, -43.8056932 ], [ -176.5416098, -43.8055476 ], [ -176.5414803, -43.8053394 ], [ -176.5413077, -43.805069 ], [ -176.5411207, -43.8048712 ], [ -176.5409624, -43.804559 ], [ -176.5407467, -43.804351 ], [ -176.5407322, -43.8041844 ], [ -176.5406171, -43.8039659 ], [ -176.5404158, -43.8038202 ], [ -176.5402001, -43.8036745 ], [ -176.5399701, -43.8034769 ], [ -176.5398583, -43.8033642 ], [ -176.5394667, -43.8030815 ], [ -176.5391362, -43.8029359 ], [ -176.5388629, -43.8027174 ], [ -176.5385179, -43.8024573 ], [ -176.5383022, -43.8023117 ], [ -176.5381009, -43.8021557 ], [ -176.5379572, -43.802062 ], [ -176.537799, -43.8019267 ], [ -176.5376552, -43.8017082 ], [ -176.5374826, -43.8015418 ], [ -176.5372669, -43.8014377 ], [ -176.5370944, -43.8013025 ], [ -176.5370368, -43.8011672 ], [ -176.5368212, -43.8009694 ], [ -176.5366446, -43.8006848 ], [ -176.5365149, -43.8004189 ] ] } }

[jwass]

You can also use the short form for the cast: connectors::JSON AS connectors although I prefer the explicit cast there.