New places dataset results in all Point[0, 0] geometry?

[go2garret]

Hello, I am trying to download the updated Places dataset, and it seems like all geometries are coming back with coordinates [0, 0]. I have used the following query in DuckDB to download.

COPY (
    SELECT
        id,
        update_time,
        version,
        CAST(names AS JSON) AS names,
        CAST(categories AS JSON) AS categories,
        confidence,
        CAST(websites AS JSON) AS websites,
        CAST(socials AS JSON) AS socials,
        CAST(emails AS JSON) AS emails,
        CAST(phones AS JSON) AS phones,
        CAST(brand AS JSON) AS brand,
        CAST(addresses AS JSON) AS addresses,
        CAST(sources AS JSON) AS sources,
        ST_GeomFromWKB(geometry)
    FROM
       read_parquet('s3://overturemaps-us-west-2/release/2024-03-12-alpha.0/theme=places/type=*/*', hive_partitioning=1)
       --read_parquet('s3://overturemaps-us-west-2/release/2024-02-15-alpha.0/theme=places/type=*/*', hive_partitioning=1)
    WHERE
        bbox.minx > -87.634938
        AND bbox.maxx < -79.974307
        AND bbox.miny > 24.396308
        AND bbox.maxy < 31.000888
    limit 1000
    ) TO 'test.geojsonseq'
WITH (FORMAT GDAL, DRIVER 'GeoJSONSeq', SRS 'EPSG:4326');

Here, I have tried two sources: 2024-03-12-alpha.0 and 2024-02-15-alpha.0, however I get the same results.

Sample result:

{ "type": "Feature", "properties": { "id": "08f44a98e6a1408d03b40a1134457ef1", "version": 0, "names": { "primary": "African Daisy Court by Ellis Exclusive Villas", "common": null, "rules": null }, "categories": { "main": "retirement_home", "alternate": [ "hotel" ] }, "confidence": 0.41048387096774186, "websites": [ "http:\/\/www.advantagevacationhomes.com\/orlando-vacation-homes\/rentals\/9003" ], "socials": [ "https:\/\/www.facebook.com\/1208821792495755" ], "emails": null, "phones": null, "brand": null, "addresses": [ { "freeform": "117 African Daisy Ct", "locality": "Davenport", "postcode": "33837-3200", "region": "FL", "country": "US" } ], "sources": [ { "property": "", "dataset": "meta", "recordId": "1208821792495755", "confidence": null } ] }, "geometry": { "type": "Point", "coordinates": [ 0.0, 0.0 ] } }

If, however I try to revert back to the original places file release 2023-07-26-alpha.0, then I get good coordinates. Here's an example:

{ "type": "Feature", "properties": { "id": "tmp_9B38D961498902C92A37A76136D8063F", "version": 0, "names": { "common": [ { "value": "Main Street Children's Dentistry at Palmetto Hospital", "language": "en" } ] }, "categories": { "main": null, "alternate": null }, "confidence": 0.6, "websites": null, "socials": null, "emails": null, "phones": null, "brand": { "names": null, "wikidata": null }, "addresses": [ { "locality": "Hialeah", "postcode": "33016", "freeform": "7150 W 20th Ave Ste 103", "region": "FL", "country": "US" } ], "sources": [ { "dataset": "msft", "property": "", "recordid": "562949957933037" }, { "dataset": "msft", "property": "\/properties\/existence", "recordid": "2251799818511206" }, { "dataset": "msft", "property": "\/properties\/existence", "recordid": "1970324837326697" }, { "dataset": "msft", "property": "\/properties\/existence", "recordid": "1407374888403194" }, { "dataset": "msft", "property": "\/properties\/existence", "recordid": "1125899911230711" } ] }, "geometry": { "type": "Point", "coordinates": [ -80.3253695, 25.8868747 ] } }

So, my question is, is the places.geometry field still supported? Am I accessing it incorrectly? I used this guide for reference: https://github.com/OvertureMaps/data/blob/main/duckdb_queries/places.sql.

Please advise. Thanks in advance :)

[mtravis]

@go2garret I copied your sql into DuckDB and it returned point coordinates for me?

I'm still using v0.10.0 which version are you on?

[go2garret]

Wow, that is interesting! I just redownloaded the DuckDB cli 0.10.1 (Windows) and tried again with the same results.

First I run

INSTALL spatial;
INSTALL httpfs; 
LOAD spatial;
LOAD httpfs;
SET s3_region='us-west-2'; 

Then I use the query above. All of my result geometries look like "geometry": { "type": "Point", "coordinates": [ 0.0, 0.0 ] }

As a workaround, I am parsing the bbox like ('POINT(' || bbox.minx || ' ' || bbox.miny || ')') AS wkt_geometry to extract the point geometry.

Thanks for testing my query :)

[mtravis]

@go2garret I just grabbed a copy of 0.10.1 and got the same error as you so it's a DuckDB issue.

[jwass]

@mtravis Did you contact duckdb about this?