Postgis array query

Question

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?

• Martin F: 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.
• sousa: @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.

Answer 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;

• sousa: 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.
• Paul Ramsey: 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.
• Paul Ramsey: You have an index on the CLCtable right? CREATE INDEX CLCtable_gix ON CLCtable USING GIST (the_geom)
• sousa: 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.