handstuyennn / geo

116 stars 7 forks source link

ST_CONTAINS gives me a SegmentationFault #18

Open nartest opened 1 year ago

nartest commented 1 year ago

Hi,

I try to check if a point is in a polygon, but it gives me a SegmentationFault :

SELECT 
    ST_CONTAINS(st_geogfromgeojson('{ "type": "Polygon", "coordinates": [[[-74.248352, 40.555548],[-73.824005, 40.555548],[-73.824005, 40.82628],[-74.248352, 40.82628],[-74.248352, 40.555548]]]}'),
    ST_MAKEPOINT(40.703546, -74.042358));

I tried with duckdb v0.7.2-dev899 88b1bfa74d, on Mac OS 13.1 (M2 processor)

Thanks a lot for your help.

blackrez commented 1 year ago

Hello,

You can remove st_geogfromgeojson from the query. Duckdb and the extension will transform the type.

SELECT      ST_CONTAINS('{ "type": "Polygon", "coordinates": [[[-74.248352, 40.555548],[-73.824005, 40.555548],[-73.824005, 40.82628],[-74.248352, 40.82628],[-74.248352, 40.555548]]]}',    ST_MAKEPOINT(40.703546, -74.042358));
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ st_contains('{ "type": "Polygon", "coordinates": [[[-74.248352, 40.555548],[-73.824005, 40.555548],[-73.824005, 40.82628],[-74.248352, 40.82628],[-74.248352, 40.555548]]]}', st_makepoint(40.703546, …  │
│                                                                                                 boolean                                                                                                  │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ false                                                                                                                                                                                                    │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
nartest commented 1 year ago

Thanks for your answer. You're right, it works.

But in fact, my real request is a little bit more complicated... I have a list of Polygons for some cities, and a list of points, and I want to know for each point the city it belongs.

These requests still fail :

SELECT  ST_CONTAINS(geometry,    ST_MAKEPOINT(40.703546, -74.042358)) FROM mytable LIMIT 10;
SELECT  ST_CONTAINS(polygon,    ST_MAKEPOINT(40.703546, -74.042358)) FROM mytable LIMIT 10;

In "mytable", the field "geometry" is a GeoJSON String, and "polygon" is a "geometry" type (the same polygon)

blackrez commented 1 year ago

Can you provide some samples of your data ? I have the same use case and it works.

handstuyennn commented 1 year ago

@nartest I updated geo extension. Please check it again and give me your feedback Thanks