Retrieve perspective property in theme=divisions, type=division_area

[Vanessa-dev-spatial]

Hi,

I am working with Overture Maps data using DuckDB. I find no data for the perspectives property in the theme=divisions, type=division_area dataset. I am aware it is an optional field, but seems strange that no data is present. I was wondering whether I am doing something wrong or if no data is there because the dataset is still a "work-in-progress".

Here the code:

[amrirasyidi]

I also encounter the same problem

SELECT 
    id,
    division_id,
    names.primary AS primary_name,
    subtype,
    region,
    bbox,
    ST_GeomFromWkb(geometry) AS geometry
FROM
    read_parquet("s3://overturemaps-us-west-2/release/2024-07-22.0/theme=divisions/type=division_area/*", hive_partitioning=true)
WHERE
    country = "ID"

Then I got zero result. Just few days ago, this query works just fine for me.

[jwass]

Hi @Vanessa-dev-spatial - you're right. Despite the field being defined in the schema - and is optional - we don't populate perspectives yet.

In Athena - select * from v2024_07_22_0 where perspectives is not null has zero rows.

[jwass]

@amrirasyidi You're describing a different issue but I don't see an obvious problem with your query. I just tried this in Duckdb and it seems to work fine

D select count(1) from read_parquet('s3://overturemaps-us-west-2/release/2024-07-22.0/theme=divisions/type=division_area/*') where country = 'ID';
100% ▕████████████████████████████████████████████████████████████▏
┌──────────┐
│ count(1) │
│  int64   │
├──────────┤
│    21470 │
└──────────┘

Are you still seeing an empty result for your query?

[jwass]

@amrirasyidi Just tried to switch your double quotes to single quotes and looks like it works for me:

D SELECT
      id,
      division_id,
      names.primary AS primary_name,
      subtype,
      region,
      bbox,
      ST_GeomFromWkb(geometry) AS geometry
  FROM
      read_parquet('s3://overturemaps-us-west-2/release/2024-07-22.0/theme=divisions/type=division_area/*', hive_partitioning=true)
  WHERE
      country = 'ID';

100% ▕████████████████████████████████████████████████████████████▏
┌──────────────────────┬──────────────────────┬──────────────────────┬──────────────┬─────────┬──────────────────────┬───────────────────────────────────────────────────────────────────────────────────────────────┐
│          id          │     division_id      │     primary_name     │   subtype    │ region  │         bbox         │                                           geometry                                            │
│       varchar        │       varchar        │       varchar        │   varchar    │ varchar │ struct(xmin float,…  │                                           geometry                                            │
├──────────────────────┼──────────────────────┼──────────────────────┼──────────────┼─────────┼──────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────┤
│ 085f2ce53fffffff01…  │ 085f2ce53fffffff01…  │ Kepulauan Mentawai   │ county       │ ID-SB   │ {'xmin': 98.596405…  │ MULTIPOLYGON (((100.6362559 -3.4724427, 100.6365467 -3.4708016, 100.6371152 -3.4682615, 100…  │
│ 085e34137fffffff01…  │ 085e34137fffffff01…  │ Tapan                │ locality     │ ID-SB   │ {'xmin': 101.05694…  │ POLYGON ((101.080399 -2.1502297, 101.0802702 -2.1504656, 101.0794087 -2.1510236, 101.079634…  │
│ 085ad63bbfffffff01…  │ 085ad63bbfffffff01…  │ Sumatera Barat       │ region       │ ID-SB   │ {'xmin': 98.23639,…  │ POLYGON ((99.8269454 0.4594439, 99.821652 0.45962, 99.817778 0.460278, 99.809781 0.462717, …  │
│ 085f23283fffffff01…  │ 085f23283fffffff01…  │ Pesisir Selatan      │ county       │ ID-SB   │ {'xmin': 100.29549…  │ MULTIPOLYGON (((100.5699742 -1.8876064, 100.5701673 -1.8875688, 100.5704677 -1.8875688, 100…  │
│ 085dcacdbfffffff01…  │ 085dcacdbfffffff01…  │ Padang               │ county       │ ID-SB   │ {'xmin': 100.09883…  │ MULTIPOLYGON (((100.1724699 -1.1372351, 100.1722338 -1.1369683, 100.1724391 -1.1366604, 100…  │
│ 085e6240ffffffff01…  │ 085e6240ffffffff01…  │ Nagari Sungai Abu    │ locality     │ ID-SB   │ {'xmin': 100.98275…  │ POLYGON ((100.9832117 -1.1842846, 100.9840968 -1.1832173, 100.9846922 -1.1821875, 100.98475…  │
│ 085e68b23fffffff01…  │ 085e68b23fffffff01…  │ Solok                │ county       │ ID-SB   │ {'xmin': 100.426, …  │ POLYGON ((101.166599 -1.102766, 101.1664377 -1.1024138, 101.16653 -1.1024155, 101.1667949 -…  │
│ 085d18993fffffff01…  │ 085d18993fffffff01…  │ Solok                │ county       │ ID-SB   │ {'xmin': 100.54146…  │ POLYGON ((100.541466 -0.808992, 100.542274 -0.809554, 100.543884 -0.810561, 100.544937 -0.8…  │
│ 085c56943fffffff01…  │ 085c56943fffffff01…  │ Pariaman             │ county       │ ID-SB   │ {'xmin': 100.07452…  │ MULTIPOLYGON (((100.1116965 -0.6598457, 100.1115858 -0.6612716, 100.1105903 -0.6618097, 100…  │
│ 085c0a9d7fffffff01…  │ 085c0a9d7fffffff01…  │ Korong Durian        │ locality     │ ID-SB   │ {'xmin': 100.28244…  │ POLYGON ((100.2827996 -0.6794624, 100.2824437 -0.6811248, 100.2825663 -0.6814177, 100.28278…  │
│ 085c0a98bfffffff01…  │ 085c0a98bfffffff01…  │ Kabun Kopi           │ locality     │ ID-SB   │ {'xmin': 100.28979…  │ POLYGON ((100.2915539 -0.677467, 100.2907568 -0.6775682, 100.2908152 -0.6777411, 100.290494…  │
│ 085c0ad67fffffff01…  │ 085c0ad67fffffff01…  │ Korong Pasa Mudiak   │ locality     │ ID-SB   │ {'xmin': 100.28544…  │ POLYGON ((100.2900517 -0.6785926, 100.2903035 -0.6785476, 100.2902395 -0.6782942, 100.29058…  │
│ 085c18c8ffffffff01…  │ 085c18c8ffffffff01…  │ Padang Pariaman      │ county       │ ID-SB   │ {'xmin': 99.96212,…  │ MULTIPOLYGON (((99.9964106 -0.7590215, 99.996711 -0.7588606, 99.9968719 -0.7588498, 99.9970…  │
│ 085c176d7fffffff01…  │ 085c176d7fffffff01…  │ Padang Panjang       │ county       │ ID-SB   │ {'xmin': 100.36533…  │ POLYGON ((100.4370676 -0.4660528, 100.4369586 -0.4661377, 100.4367154 -0.4663566, 100.43664…  │
...

[jwass]

@Vanessa-dev-spatial @amrirasyidi I'm going to close this out but if you still have problems feel free to reopen or file a new issue.

[amrirasyidi]

It works now, I changed to single quotes as well. Thanks!