Saturday 17 August 2019

postgis - Find all polygons containing points specific criteria


I have two tables (PostGIS), one containing points, and the other containing polygons.


Right now i have this query for finding polygons (Laravel framework):


        $polygon = DB::connection('pgsql')
->select(DB::raw("SELECT row_to_json(fc)
FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
FROM (SELECT 'Feature' As type

, ST_AsGeoJSON(ST_Transform(geom,4326))::json As geometry
, row_to_json((SELECT l FROM (SELECT areal) As l
)) As properties
FROM jbb2013 As lg WHERE gid = 1208749 OR gid = 692891 OR gid = 993224 OR gid = 1171857 OR gid = 1176873) As f ) As fc;")
);

With some work i get the results as GeoJSON, i then send that to Leaflet.


As you can see i specify the the polygons by id. That's not what i want to do. I'd like to use the other table containing points and find the points by a specific customer_id.


So something like this: Find all points that belong to a specific customer_id Use those points in a ST_Contains query to find the polygons that contain the points. The resulting GeoJSON should then only contain the polygons found by the query.


Result (jbb2013 is the polygons table and samples is where the points are stored):



$polygon = DB::connection('pgsql')
->select(DB::raw("SELECT row_to_json(fc)
FROM ( SELECT 'FeatureCollection' AS type, array_to_json(array_agg(f)) AS features
FROM (SELECT 'Feature' AS type
, ST_AsGeoJSON(ST_Transform(lg.geom,4326))::json AS geometry
, row_to_json((SELECT l FROM (SELECT areal) AS l
)) AS properties
FROM jbb2013 AS lg, (SELECT geom FROM samples WHERE customer_id = : customer_id) AS pts WHERE ST_Intersects(lg.geom, pts.geom)) AS f ) AS fc;"),
array('customer_id' => $customer_id)
);


Answer



Don't be too alarmed by the GeoJSON producing SQL -- I know how cross-eyed it can make one. Ultimately, you have a table jbb2013 in the final FROM clause, and you want to limit that to those points that it intersects based on a customer_id, so you can do a standard spatial join, as a sub-query where you select only those points that match the particular customer_id, using ST_Intersects in the WHERE to limit the results. I don't know what you points table is called, so I have called it points.


$polygon = DB::connection('pgsql')
->select(DB::raw("SELECT row_to_json(fc)
FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
FROM (SELECT 'Feature' As type
, ST_AsGeoJSON(ST_Transform(geom,4326))::json As geometry
, row_to_json((SELECT l FROM (SELECT areal) As l
)) As properties
FROM jbb2013 As lg, (SELECT geom from points where customer_id in (1,2,3) as pts WHERE ST_Intersects(lg.geom, pts.geom) As f ) As fc;")

);

All the json aggregate functions make such queries hard to read, but if you just focus on the final FROM/WHERE, you can do what you want normally.


No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...