Logic behind CRS in def convert_pbf_to_parquet

[huelar]

Hi
We're wondering why OGC:CRS84 is used instead of EPSG:4326 for the definition to convert the pbf to a parquet file.
Is there a way how to convert it in EPSG:4326 during the extract or convert process?

[RaczeQ]

Hi @huelar
The OGC:CRS84 and EPSG:4326 (WGS84) are equivalent in value domains (degrees), but the order is changed (EPSG:4326 has latitude/longitude, and OGC:CRS84 has longitude/latitude).

I don't pass CRS explicitly, because the GeoParquet specification has it covered and I follow the X/Y order of coordinates (lon/lat):

https://github.com/opengeospatial/geoparquet/blob/main/format-specs/geoparquet.md#column-metadata

crs: If the field is not provided, the default CRS is OGC:CRS84, which means the data in this column must be stored in longitude, latitude based on the WGS84 datum.

[huelar]

Do you have an example how we can define it in a CLI?
--crs "EPSG:4326"?

[RaczeQ]

Currently, there is no option to change the output CRS of the Parquet file in QuackOSM.

For now, you can use the DuckDB Spatial extension on the existing and use the ST_Transform function:

import duckdb

duckdb.install_extension('spatial')
duckdb.load_extension('spatial')

relation = duckdb.sql(
    # DuckDB Spatial doesn't save the CRS in Parquet schema, but it will transform the coordinates
    """
    SELECT * REPLACE (ST_Transform(geometry, 'OGC:CRS84', 'EPSG:4326') as geometry)
    FROM read_parquet('files/36ddb61c_ae99e3d9_exploded_sorted.parquet')
    """
)

relation.to_parquet('files/36ddb61c_ae99e3d9_exploded_sorted_epsg_4326.parquet')

Output:

┌────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬────────────────┐
│   feature_id   │                                                                                                                                                                 geometry                                                                                                                                                                 │    building    │
│    varchar     │                                                                                                                                                                 geometry                                                                                                                                                                 │    varchar     │
├────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼────────────────┤
│ way/166135774  │ POLYGON ((50.001271 14.298794, 50.001249 14.298806, 50.001228 14.298696, 50.00125 14.298684, 50.001271 14.298794))                                                                                                                                                                                                                       │ yes            │
│ way/465649732  │ POLYGON ((49.9977332 14.30329, 49.9977645 14.3032822, 49.9977706 14.3033412, 49.9977624 14.3033432, 49.9977672 14.3033913, 49.9977612 14.3033929, 49.9977627 14.3034057, 49.9977551 14.3034092, 49.9977527 14.3033969, 49.9977269 14.3034048, 49.9977154 14.3032944, 49.9977105 14.3032775, 49.9977313 14.3032726, 49.9977332 14.30329)) │ residential    │
│ way/104625751  │ POLYGON ((49.9966104 14.3090153, 49.9966757 14.3089596, 49.9967316 14.3091182, 49.9966861 14.3091569, 49.9966662 14.3091739, 49.9966104 14.3090153))                                                                                                                                                                                     │ yes            │
│ way/55798658   │ POLYGON ((49.9969271 14.3091199, 49.996906 14.3091424, 49.9969215 14.3091776, 49.9969109 14.3091889, 49.996964 14.3093066, 49.9969116 14.3093549, 49.9967774 14.3090632, 49.9968376 14.308988, 49.9968771 14.3090772, 49.9968887 14.3090658, 49.9968931 14.3090766, 49.9969023 14.3090674, 49.9969271 14.3091199))                       │ farm_auxiliary │
│ way/207291670  │ POLYGON ((49.996973 14.3093374, 49.9968376 14.3094817, 49.9968723 14.3095604, 49.9970077 14.309416, 49.996973 14.3093374))                                                                                                                                                                                                               │ yes            │
│ way/55798659   │ POLYGON ((49.9969509 14.3095452, 49.9969802 14.3096175, 49.9969233 14.3096758, 49.9968939 14.3096022, 49.9969509 14.3095452))                                                                                                                                                                                                            │ yes            │
│ way/868636312  │ POLYGON ((50.0005251 14.3107696, 50.0005706 14.3107747, 50.0005671 14.3108454, 50.0005217 14.3108404, 50.0005251 14.3107696))                                                                                                                                                                                                            │ yes            │
│ way/465649733  │ POLYGON ((50.0006115 14.3118901, 50.0006133 14.3119359, 50.0005451 14.3119463, 50.0005429 14.3118993, 50.0006115 14.3118901))                                                                                                                                                                                                            │ yes            │
│ way/465649734  │ POLYGON ((50.0005618 14.3123512, 50.0005654 14.3124064, 50.0005179 14.3124158, 50.0005138 14.3123608, 50.0005366 14.3123563, 50.0005344 14.3123332, 50.000566 14.3123268, 50.0005683 14.3123496, 50.0005618 14.3123512))                                                                                                                 │ yes            │
│ way/101475916  │ POLYGON ((49.9988563 14.3176665, 49.998794 14.3176892, 49.9987403 14.3173483, 49.9988024 14.3173248, 49.9988563 14.3176665))                                                                                                                                                                                                             │ residential    │
│       ·        │                                                              ·                                                                                                                                                                                                                                                                           │   ·            │
│       ·        │                                                              ·                                                                                                                                                                                                                                                                           │   ·            │
│       ·        │                                                              ·                                                                                                                                                                                                                                                                           │   ·            │
│ way/380214039  │ POLYGON ((49.9888848 14.3526482, 49.9888727 14.3526876, 49.9888231 14.3526504, 49.9888348 14.3526107, 49.9888848 14.3526482))                                                                                                                                                                                                            │ garage         │
│ way/1034779482 │ POLYGON ((49.9887988 14.3527299, 49.9888483 14.3527669, 49.9888369 14.3528043, 49.9887875 14.3527673, 49.9887988 14.3527299))                                                                                                                                                                                                            │ garage         │
│ way/93558015   │ POLYGON ((49.9888249 14.3528434, 49.9888129 14.3528822, 49.9887638 14.3528455, 49.9887755 14.3528065, 49.9888249 14.3528434))                                                                                                                                                                                                            │ garage         │
│ way/824452734  │ POLYGON ((49.9888369 14.3528043, 49.9888249 14.3528434, 49.9887755 14.3528065, 49.9887875 14.3527673, 49.9888369 14.3528043))                                                                                                                                                                                                            │ garage         │
│ way/93558018   │ POLYGON ((49.9896311 14.3525409, 49.9896296 14.3525156, 49.9896117 14.3525165, 49.9896093 14.3524769, 49.9896271 14.3524742, 49.9896269 14.3524727, 49.9896924 14.3524662, 49.989695 14.3525344, 49.9896311 14.3525409))                                                                                                                 │ residential    │
│ way/93558008   │ POLYGON ((49.9898198 14.3524481, 49.9898234 14.3525174, 49.9897147 14.3525337, 49.9897099 14.3524623, 49.9898198 14.3524481))                                                                                                                                                                                                            │ residential    │
│ way/94687036   │ POLYGON ((49.9900317 14.3524751, 49.9899453 14.3525011, 49.98994 14.3524628, 49.9899272 14.352467, 49.9899202 14.3524163, 49.989933 14.3524121, 49.9899232 14.352341, 49.9900093 14.3523146, 49.9900317 14.3524751))                                                                                                                     │ residential    │
│ way/94687039   │ POLYGON ((49.9899532 14.3520669, 49.9899562 14.3520886, 49.9899765 14.352084, 49.9899813 14.3521279, 49.9899612 14.3521333, 49.9899668 14.3521829, 49.989895 14.3522026, 49.9898814 14.3520878, 49.9899532 14.3520669))                                                                                                                  │ residential    │
│ way/93558020   │ POLYGON ((49.9898081 14.3522124, 49.9898235 14.3523371, 49.9897433 14.3523613, 49.9897376 14.3523157, 49.9897639 14.352308, 49.9897584 14.3522624, 49.9897543 14.3522281, 49.9898081 14.3522124))                                                                                                                                        │ residential    │
│ way/93558014   │ POLYGON ((49.9897906 14.3520636, 49.9898053 14.3521864, 49.9897401 14.3522048, 49.9897321 14.3521428, 49.9896949 14.3521522, 49.9896894 14.3521055, 49.9896767 14.3521075, 49.9896744 14.3520919, 49.9897906 14.3520636))                                                                                                                │ residential    │
├────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────────────┤
│ ? rows (>9999 rows, 20 shown)                                                                                                                                                                                                                                                                                                                                    3 columns │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

[huelar]

Thank you, that worked!