DuckDB Spatial and Observable Framework/Plot geo

[fundef1]

Hi, i'm playing around with the latest Observable Framework release and the spatial extension, and I can't get it to display any feature from an ESRI shapefile I loaded into duckdb with the spatial extension. I got as far as the tip displaying but nothing else seems to render. here's a whittled down example (manually wrangling the geom into geojson is terribly cumbersome, but at least I got something.

Any pointers in the right direction would be much appreciated. thanks!

// copy paste for EPSG:3812 projection (Belgian Lambert 2008)
// EPSG:31370 also available
const projection = d3.geoConicConformal()
   .parallels([49.8333333333333, 51.1666666666667])
   .rotate([-4.35921583333333, 0])
   .center([0, 50.797815])
   .scale(11000);

view(Plot.plot(
  { 
   projection: projection,
   marks: [
     Plot.geo(ST_Point, {tip: true, 
     //r: "val", //causes tip not to render?
     //title: "name" // err: is not iterable at SVGTextElement
     }),
    Plot.geo(POLYGON, {tip: true, 
     //title: "name" // err: is not iterable at SVGTextElement
     })
   ]
 }));

SELECT CAST({
	type: 'Feature', 
	geometry: ST_AsGeoJSON(ST_Point(0,50.797815)), 
	properties: { 
		name: 'my_point' 
	} 
} AS JSON) AS ST_Point;

SELECT CAST({
	type: 'Feature', 
	geometry: ST_AsGeoJSON(
    'POLYGON Z((651744.2216000035 703170.8317000009 0, 651731.8492999971 703149.4710000008 0, 651731.3493999988 703148.5960000008 0, 651729.068599999 703144.7362999991 0, 651710.4631000012 703113.2657999992 0, 651671.8466000035 703045.0896999985 0, 651655.7722000033 703013.9475000016 0, 651629.5429000035 702973.2417000011 0, 651569.0869000033 703002.3592000008 0, 651544.4179999977 702977.6845000014 0, 651543.5119000003 702976.7782000005 0, 651537.5438999981 702970.9180999994 0, 651510.2817000002 702944.1339000016 0, 651477.2387999967 702913.864599999 0, 651443.617899999 702881.6733000018 0, 651440.3994000033 702879.0322999991 0, 651420.6670999974 702862.8894999996 0, 651406.4152000025 702879.2314000018 0, 651403.1960000023 702882.9340000004 0, 651370.6771000028 702913.4142999984 0, 651343.4395999983 702940.8640000001 0, 651322.4843000025 702961.3770000003 0, 651300.8096999973 702985.2179999985 0, 651277.1664000005 703009.5899999999 0, 651245.1933000013 703047.3044999987 0, 651222.2114000022 703029.7862999998 0, 651219.2893000022 703030.8797000013 0, 651187.3081 703005.9387000017 0, 651153.9072000012 702963.7162999995 0, 651181.987999998 702940.4228000008 0, 651201.3343999982 702915.1129000001 0, 651204.1942000017 702911.378899999 0, 651216.5864000022 702897.1616999991 0, 651183.8082000017 702874.3612000011 0, 651182.7770999968 702873.6422999986 0, 651161.5446000025 702858.8898000009 0, 651155.7639999986 702854.8735999987 0, 651116.9866999984 702824.0724 0, 651086.1937000006 702793.2719999999 0, 651049.6983999982 702755.6273999996 0, 651026.8888999969 702732.8123999983 0, 650996.0957999974 702702.0119999982 0, 650966.443400003 702672.3524999991 0, 650952.3513000011 702659.4915000014 0, 650940.1340999976 702648.3495999984 0, 650918.9442000017 702669.3781000003 0, 650885.1433999985 702704.7487999983 0, 650840.7167000026 702747.5868999995 0, 650825.6516000032 702771.3660999984 0, 650786.2877999991 702810.6110999994 0, 650741.3012000024 702832.0744000003 0, 650747.7666999996 702859.262600001 0, 650754.3567999974 702889.7316999994 0, 650760.5546000004 702933.4508999996 0, 650704.1931999996 702953.8658000007 0, 650692.6614999995 702958.9114000015 0, 650688.2238999978 702960.8482000008 0, 650632.1124000028 702982.1539999992 0, 650633.891900003 702996.4508999996 0, 650637.1542999968 703022.5760999992 0, 650642.3386000022 703050.7014999986 0, 650650.6137000024 703100.7490000017 0, 650654.9224999994 703130.9992999993 0, 650659.2005999982 703158.686999999 0, 650667.1488000005 703198.3126000017 0, 650673.8620000035 703244.563099999 0, 650686.072800003 703310.8608000018 0, 650694.0209999979 703350.7364000008 0, 650702.0768999979 703406.3933000006 0, 650707.2128999978 703444.5029999986 0, 650715.3957000002 703482.5973000005 0, 650717.1693999991 703547.7219999991 0, 650724.9905999973 703603.878899999 0, 650766.2092000023 703602.8524000011 0, 650825.3960999995 703604.875 0, 650863.7395000011 703603.5672000013 0, 650897.5199000016 703610.0555999987 0, 650944.3616999984 703621.5921999998 0, 650984.5634000003 703632.0500999987 0, 651030.4209999964 703643.7584999986 0, 651072.7316000015 703656.2635000013 0, 651128.6824000031 703672.7388000004 0, 651172.4310000017 703680.9783000015 0, 651217.3042000011 703693.2023999989 0, 651245.2400999963 703704.1429999992 0, 651314.1601999998 703715.6510999985 0, 651321.2584000006 703677.5898999982 0, 651332.4685999975 703616.8260999992 0, 651339.3175999969 703572.3429000005 0, 651346.1660000011 703533.5626000017 0, 651351.8730000034 703499.3449999988 0, 651357.5829999968 703442.3147 0, 651361.2136999965 703394.1436999999 0, 651391.5442999974 703371.5069000013 0, 651422.7327999994 703360.0731000006 0, 651465.8284000009 703342.6878000014 0, 651483.5443999991 703364.6897 0, 651517.6067000031 703362.8969999999 0, 651547.7643000036 703349.1662999988 0, 651586.7819000036 703330.0304999985 0, 651631.9394000024 703317.9734000005 0, 651617.6011999995 703272.0346000008 0, 651640.2299000025 703238.7875999995 0, 651667.0135999992 703216.6972000003 0, 651699.6563000008 703196.8418000005 0, 651725.7364000008 703179.1576999985 0, 651727.2832000032 703178.4547000006 0, 651744.2216000035 703170.8317000009 0))'
    ::GEOMETRY), 
	properties: { 
		name: 'my_poly',
		val: '55'  
	} 
} AS JSON) AS POLY;

[Fil]

There are a couple of difficulties here.

The coordinates in this dataset are already projected to a metric plane, so you should not use another projection (which would map from longitude/latitude in degrees), but use linear scales for x and y, with an aspectRatio of 1 to avoid distorsion. (Or use the "identity" or "reflect-y" projection to be explicit and remove the axes. See https://observablehq.com/plot/features/projections.)

The DuckDB client does not decode JSON, which is returned as a string, so you have to parse it on the side (this could be improved in the future, when a geometry is a string?). I recommend parsing this into a proper GeoJSON FeatureCollection, even though it's not strictly necessary.

This gives:

```sql id=poly echo
-- extract the data
SELECT {
  type: 'Feature',
  geometry: ST_AsGeoJSON('POLYGON Z((651744. (………) 651744.2216000035 703170.8317000009 0))'),
  properties: {name: 'my_poly', val: 55}
}::json AS geom;
```

```js echo
// parse into live GeoJSON
const polygons = {type: "FeatureCollection", features: Array.from(poly, ({geom: g}) => JSON.parse(g))};
```

```js echo
// render the map
Plot.plot({
  marginLeft: 60,
  aspectRatio: 1,
  marks: [
    Plot.geo(polygons, {fill: "val", title: "name"})
  ]
})
```

[fundef1]

Hej Fil, thanks for following up.
I use the code you pasted, with the full polygon data, but still/again no luck. (I also removed the init code I had and loaded the extensions via the config file - full paranoia mode by now)

I attached the complete .md
FIL.md
file, but as far as I can tell it is exactly what you posted....

[Fil]

See it in action https://observablehq.observablehq.cloud/pangea/support/duckdb-spatial-1825

[Fil]

Your file works on my machine… I've also tested three browsers (Firefox, Safari, and Chrome). I don't know what to do to debug this. Can you publish your project? Maybe it doesn't work on your OS platform?

[fundef1]

the link you posted works on observablehq works for me. so it must be something on my end (chrome on windows, observable platform is served from a remote linux machine ssh tunneled to localhost.) I also treid a deploy and server it straight from the windows machine with the same result.

I'll setup a clean project and report back here.

[fundef1]

Okay, it works now: npm run clean fixed it. 🤦🏻‍♂️
Thanks for the support, I learned a a lot about projections and geojson today, and a little about a clean environment. 😄