0

I have two tables in Postgis 2.1 database:

  • Polygon features with a reach attributive information;
  • Regular point grid;

I would like to get a SQL query returning the regular point grid with assigned attributive information from the underlying polygon. The output should contain lat, lon columns and an extra column with a given attribute. This will be passed to R scripts throughout the csv files.

Before:

enter image description here

After:

enter image description here

1
Select points.*, polygons.* from points 
inner join polygons on
st_intersects(points.geom,polygons.geom);
1
SELECT ST_X(pts.geom) as lon, ST_Y(pts.geom) as lat, poly.attribute 
FROM
  points pts, polygons poly
WHERE ST_Intersects(poly.geom, pts.geom);

where it is assumed that the attribute you want from the polgons table is called attribute. For non-trivial table sizes, you will want a spatial index on the geometry columns of polygons/points tables.

  • Is this what you were looking for, or did you need snap to grid functionality also? – John Powell Oct 18 '14 at 9:43

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.