1

I am trying to create a layer that contains all of the businesses in Los Angeles proper. I have a shapefile for the polyon of the LA boundaries (shp_laboundaries) and I have a set of points in the la region for the businesses (tmp_coords). So I want to do a query where I can find all of the points that fall within this polygon. In fact I want to create a new layer from these points.

I was having trouble getting the st_within query to reference a separate layer in postgis. I was trying a few different queries, but hopefully someone can tell me the correct form.

select * from tmp_coords where st_within(tmp_coords.st_loc, public."shp_laboundary");

or

 select * from tmp_coords where st_within(st_loc, select geom from shp_laboundary);
1

If your LA boundary data consists of 1 single polygon, it's easy:

SELECT a.* FROM tmp_coords AS a INNER JOIN shp_laboundary AS b ON (St_Intersects(a.st_loc, b.geom))

You can safely use intersects, it's faster than within. If you have multiple polygons you first have to merge them with ST_Collect

  • Great. Thanks for the tip on st_intersects() too. That is useful. – krishnab Jan 25 '16 at 22:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.