**What it does:**
Directly queries a **WFS GeoJSON API** (in this example: [_Transport for Cairo_](https://data.transportforcairo.com/catalogue/#/dataset/96)), explodes features, and converts their geometries into WKB Geometries ... all inside DuckDB using the Spatial extension in one query.
This pattern works with any WFS/GeoJSON endpoint including **#ArcGIS** Server, **#GeoServer**, **#QGIS** Server, **#Mapserver** ... etc.
![[Pasted image 20251124153426.png]]
---
## **SQL**
```sql
-- Enable Spatial and HTTPFS (install only once, load every session)
INSTALL spatial;
LOAD spatial;
INSTALL httpfs;
LOAD httpfs;
-- Query a WFS GeoJSON (Transport for Cairo)
SELECT
unnest(feature.properties) AS properties,
ST_GeomFromGeoJSON(feature.geometry) AS geometry
FROM (
SELECT unnest(features) AS feature
FROM read_json_auto(
'https://data.transportforcairo.com/geoserver/geonode/ows?'
'service=WFS'
'&version=1.1.0'
'&request=GetFeature'
'&typeName=geonode:cairo_od_stats'
'&maxFeatures=26154'
'&outputFormat=application/json'
)
);
```
---
## DuckDB-WASM Shell - **Try it live:**
🔗 [https://cutt.ly/kttkWKva](https://cutt.ly/kttkWKva)
<iframe src="https://shell.duckdb.org/#queries=v0,INSTALL-spatial~%0ALOAD-spatial~%0AINSTALL-httpfs~%0ALOAD-httpfs~%0A%0ASELECT-%0A----unnest(feature.properties)-AS-properties%2C%0A----ST_GeomFromGeoJSON(feature.geometry)-AS-geometry%0AFROM-(%0A----SELECT-unnest(features)-AS-feature%0A----FROM-read_json_auto(%0A--------'https%3A%2F%2Fdata.transportforcairo.com%2Fgeoserver%2Fgeonode%2Fows%3F'%0A--------'service%3DWFS'%0A--------'%26version%3D1.1.0'%0A--------'%26request%3DGetFeature'%0A--------'%26typeName%3Dgeonode%3Acairo_od_stats'%0A--------'%26maxFeatures%3D5'%0A--------'%26outputFormat%3Dapplication%2Fjson'%0A----)%0A)~" width="100%" height="740" style="border: 1px solid black;"> </iframe>
---
<iframe src="https://kepler-preview.foursquare.com/?sql=INSTALL+spatial%3B%0ALOAD+spatial%3B%0A%0ASELECT+%0A++++unnest%28feature.properties%29+AS+properties%2C%0A++++ST_GeomFromGeoJSON%28feature.geometry%29+AS+geometry%0AFROM+%28%0A++++SELECT+unnest%28features%29+AS+feature%0A++++FROM+read_json_auto%28%0A++++++++%27https%3A%2F%2Fdata.transportforcairo.com%2Fgeoserver%2Fgeonode%2Fows%3F%27%0A++++++++%27service%3DWFS%27%0A++++++++%27%26version%3D1.1.0%27%0A++++++++%27%26request%3DGetFeature%27%0A++++++++%27%26typeName%3Dgeonode%3Acairo_od_stats%27%0A++++++++%27%26maxFeatures%3D26154%27%0A++++++++%27%26outputFormat%3Dapplication%2Fjson%27%0A++++%29%0A%29%3B%0A&mapUrl=https%3A%2F%2Flink.storjshare.io%2Fraw%2Fjul4mlwck3ssbmiixe2scerk3dba%2Ftruemaps-public%2Fkepler%2Fduckdb-snippet-3.json" width="100%" height="740" style="border: 1px solid black;"> </iframe>
___
#Linkedin : https://www.linkedin.com/posts/yharby_duckdb-wfs-geojson-activity-7398765430099591169-dYdD