0

I have a Postgis database with land cover vector data with the following layout:

select * from CLCtable limit 0;

outputs:

gid | code_06 | id | remark | area_ha | shape_leng | shape_area | the_geom

My problem is the following: I have a 2D point array and I want to get the 'code_06' value for each point. I'm currently doing a query inside a for loop that goes through all the points. I end up with a query for each point which takes too much time if I have for instance a 100x100 2D point array.

The query I'm doing is something like this:

select a.code_06 from CLCtable as a 
where ST_Contains(a.the_geom, ST_GeomFromText('POINT(1234 4567)', 3035));

Is there some way to this more efficiently?

2
  • Could your 2D array possibly be re-formulated as a raster by any chance? I've no experience with any, but, if so, maybe a PostGIS raster process could help.
    – Martin F
    Nov 9, 2013 at 21:17
  • @martinf, I've used raster functions with postgis and it was painful. I tried to create a polygon, clip the vector maps and rasterize the clipped table. At this point it became quite messy and I couldn't menage to make it work.
    – sousa
    Nov 12, 2013 at 11:43

1 Answer 1

1

One way would be to put your array of points into a table and do a join. You can do it in one big gob of SQL by creating the points relation in the query.

WITH pts AS (
  SELECT ST_SetSRID(ST_MakePoint(x,y),3035) AS the_goem
  FROM (VALUES 
    (1,1),
    (2,2),
    (4,5)
  ) as t(x,y)
) 
SELECT 
  a.code_06, 
  ST_AsText(pts.the_geom) AS pt
FROM CLCtable a 
JOIN pts
ON ST_Contains(a.the_geom, pts.the_geom);

You could also use a TEMP table, for simpler (maybe?) looking code.

CREATE TEMP TABLE pts (the_geom GEOMETRY);
INSERT INTO pts VALUES (ST_SetSRID(ST_MakePoint(1,2),3035));
INSERT INTO pts VALUES (ST_SetSRID(ST_MakePoint(3,4),3035));
SELECT a.code_06, 
  ST_AsText(pts.the_geom) AS pt
FROM CLCtable a 
JOIN pts
ON ST_Contains(a.the_geom, pts.the_geom);
DROP TABLE pts;
4
  • HI @Paul, I've tested your suggestion against my current query and it took approximately the same time. I guess postgis takes the same time doing st_Contains one query at the time or all in one big query.
    – sousa
    Nov 12, 2013 at 11:36
  • I'm a bit surprised, I figured our geometry caching system would make the join approach much faster then the one-at-a-time, unless your land cover polygons are quite simple. Nov 12, 2013 at 17:19
  • You have an index on the CLCtable right? CREATE INDEX CLCtable_gix ON CLCtable USING GIST (the_geom) Nov 12, 2013 at 17:20
  • I'm using corine land cover 2006 data eea.europa.eu/data-and-maps/data/clc-2006-vector-data-version-2. Yes, I've indexed the table.
    – sousa
    Dec 12, 2013 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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