3

I have two tables : country and regions. Country contains all the Europeans countries and regions contains some subdivisions of those countries. I created a small Union of some countries and regions.

SELECT ST_UNION(geom) AS poly 
FROM (
    SELECT reg."id", reg."geom" 
    FROM "public"."regions" AS reg 
    WHERE reg."id" in (1, 10, 12) 
    UNION
    SELECT c."id", c."geom"
    FROM "public"."country" AS c
    WHERE c."id" IN (14, 15, 13)) AS geom_union

And now I want to test if a point (for instance (3, 46) roughly the middle of France) is in the result polygon of my union.

So I tried :

SELECT st_contains(
    SELECT ST_UNION(geom) AS poly 
    FROM (
        SELECT reg."id", reg."geom" 
        FROM "public"."regions" AS reg 
        WHERE reg."id" in (1, 10, 12) 
        UNION
        SELECT c."id", c."geom"
        FROM "public"."country" AS c
        WHERE c."id" IN (14, 15, 13)) AS geom_union,
    SELECT ST_GeomFromText('POINT(3 46)') AS point
)

But for some reason I don't understand, i get this error :

syntax error at or near "SELECT"
LINE 2:  SELECT ST_UNION(geom) AS poly 
         ^
  • Also, I am new to postGIS and postgresQL, feel free to link me some good tutorial – Louis Aug 29 '18 at 13:19
  • what is POINT(3 46)? it does not look like a real coordinate.Bruère-Allichamps the centre of France, coordinates are 46.7692,2.4442 and your projection so postgres needs Select ST_GeomFromText('POINT(46.7692,2.4442)', 4326) if your data is in WGS84 (Geographic) – Mapperz Aug 29 '18 at 13:39
  • The point creation syntaxe may be wrong, but I don't believe this is the reason of the error postgresQL returns me. – Louis Aug 29 '18 at 13:44
  • 2
    try use parentheses inside every subquery – Jane Aug 29 '18 at 13:49
  • @Mapperz: Remember that coordinates in PostgreSQL typically come in (lon, lat) order, so POINT(3 46) seems about right for "somewhere in France". – Ture Pålsson Aug 29 '18 at 13:54
5

When using a SELECT statement as an expression, as you are doing with the parameters to ST_Contains, you need to wrap it in parentheses. Also, in the case of the SELECT GeomFromText(...), what you are SELECT:ing is already an expression, so you can simply remove the SELECT.

So, unless there's something else I'm overlooking, the following should work:

SELECT st_contains(
    (SELECT ST_UNION(geom)
     FROM (
         SELECT reg."id", reg."geom" 
         FROM "public"."regions" AS reg 
         WHERE reg."id" in (1, 10, 12) 
         UNION
         SELECT c."id", c."geom"
         FROM "public"."country" AS c
         WHERE c."id" IN (14, 15, 13)) AS geom_union),
    ST_GeomFromText('POINT(3 46)', 4326)
)
1

Since I had a few minutes I´d like to add up to this with a few words about the core concept of index aware queries (SARGable, for Search ARGument -able; make use of any index on search columns):

Indexes on search columns is a fundamental element in database design and for the complex geometric relation analysis in PostGIS in particular. Having a (spatial) index in place and utilize it properly is what makes a query run in 2 ms instead of 5 min (not as a rule...you get the point).

In PostGIS, the primarily used GIST index for geometries essentially creates a list of bounding boxes for each geometry; when the respective table is then searched for spatial relations (e.g. ST_Contains), the planner can instead look up the index list and compare bounding boxes (thus geometries consisting of only 4 (or, really, 5) points) instead of tens, hundreds or thousands of vertices that make up the real geometry.

This being said, your query structure, however perfectly valid (assuming the corrections posted as the above answer), denies the planner to utilize the index in the search; the main problem is ST_Union.

ST_Union effectively creates a new (more complex) geometry that can not be indexed on the fly. The operation itself is costly. And for every search operation (spatial relation) it will now need to traverse every vertex in the resulting geometry. This is also a reason why, in general, having Multi geometries is bad practise performancewise; you can index them alright, but in the final comparison, the number of distinct geometries and their number of vertices is unproportionally larger than for Simple geometries...somewhat defeating the purpose of an index.

I´m still not quite sure if an UNION ALL select over two tables will actually keep the result fully SARGable, but the smaller geometries at least will make a difference. You won´t necessarily notice that on the small scale of your search, but with just a few polygons more you can see the execution time rise (exponentially, even).

The following structure is only one of many possible, but should be reasonably close to yours to be comprehensive and benefits from what I said above. It avoids the need of a single MultiPolygon by comparing the set of rows created in the FROM block (and simulates the true/false by either printing true if a match is found or simply returning no row if no match was found):

SELECT true AS is_within    -- you can select all columns mentioned in the subquery here, e.g. `id`...
FROM (
    SELECT geom             -- ...but no need to reference the `id` column if you don´t need it
    FROM public.regions
    WHERE id IN (1, 10, 12)
    UNION ALL
    SELECT geom             -- (same here)
    FROM public.country
    WHERE id IN (14, 15, 13)
) AS polys
WHERE ST_Contains(
    polys.geom,
    ST_MakePoint(3, 46)     -- if possible, use this, as it is faster and more precise
)

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.