2

I have two polygon tables, 'cities' with ~5600 rows and 'lte_coverage' with ~469314 rows. I need to know wich cities have LTE Coverage.

Using ArcMap, I get this answer in about a minute, using 'Select By Location', but using PostGIS it takes really long, indeed after 90 minutes I cancelled the query. I am wondering what can I do to get a better performance on PostGIS.

Table cities:

                                                        Table "public.cities"
   Column   |          Type           |                           Modifiers                            | Storage  | Stats target | Description
------------+-------------------------+----------------------------------------------------------------+----------+--------------+-------------
 id         | integer                 | not null default nextval('cities_gid_seq'::regclass)           | plain    |              |
 geom       | geometry(MultiPolygonM) |                                                                | main     |              |

Indexes:
    "cities_pkey" PRIMARY KEY, btree (id)
    "cities_geom_idx" gist (geom)

Table lte_coverage:

                                                        Table "public.lte_coverage"
   Column   |          Type          |                              Modifiers                              | Storage | Stats target | Description
------------+------------------------+---------------------------------------------------------------------+---------+--------------+-------------
 gid        | integer                | not null default nextval('lte_coverage_gid_seq'::regclass)          | plain   |              |
 geom       | geometry(Polygon,4326) |                                                                     | main    |              |

Indexes:
    "lte_coverage_pkey" PRIMARY KEY, btree (id)
    "lte_coverage_geom_idx" gist (geom)

I am using this query

SELECT distinct c.id FROM cities c, lte_coverage l WHERE ST_Intersects(c.geom,l.geom)

And I have this explain output:

HashAggregate  (cost=304837.32..304892.99 rows=5567 width=8)
  Group Key: c.id
  ->  Nested Loop  (cost=0.29..302660.09 rows=870890 width=8)
        ->  Seq Scan on cities c  (cost=0.00..4715.67 rows=5567 width=40)
        ->  Index Scan using lte_coverage_geom_idx on lte_coverage l  (cost=0.29..53.36 rows=16 width=1132)
              Index Cond: (c.geom && geom)
              Filter: _st_intersects(c.geom, geom)

I am using

gisdb01=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)

gisdb01=# select postgis_full_version();
                                                                            postgis_full_version

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----
 POSTGIS="2.2.4 r15258" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RAS
TER
(1 row)

So, is there anything I can do to PostGIS perform better?

Improve this question
5
  • Without some pictures of the layers, I'm just guessing, but it sounds like you have very little index selectivity working in your favour, effectively turning the problem into a cartesian join. The solution for these big overlay problems usually involve using something like ST_SubDivide() to turn large polygons into collections of smaller ones. Pictures would help. If you can get the answer from ArcGIS, why do you need it from PostGIS? It looks like a one-time query (all cities w/ LTE coverage)
    – Paul Ramsey
    Jan 12, 2017 at 23:32
  • @PaulRamsey Thanks, I added two pictures. I need it from PostGIS due to license limitations. Its not a onte-time query, due to lot of updates in coverage and this is one problem, but I have many others analysis on the same line. I'll try st_subdivide () and post results. Thanks in advance.
    – Diego Henrique
    Jan 13, 2017 at 14:48
  • Are both layers the same projection? What does SELECT substring(st_astext(geom), 1, 100) return for each table?
    – Paul Ramsey
    Jan 13, 2017 at 15:42
  • Yes they are, the query returns "POLYGON((-54.1855413146075 -31.8017097831177,-54.1855418553933 -31.8019803360162,-54.185858659753 -3" for LTE coverage and "MULTIPOLYGON M (((-61.4134740395617 -13.2341685497038 -1.79769313486232e+308,-61.4150501174927 -13.2" for cities. I tried ST_SubDivide() and it worked like a charm! I created two tables using ST_SubDivide() and the query ran in ~3minutes, and ~4 minutes even if I use the original cities table. Thank you so much!
    – Diego Henrique
    Jan 13, 2017 at 16:36
  • That's good to hear, I wasn't sure it would work, the pictures didn't really make it clear if there were serious size/complexity issues on an individual geometry level: apparently there were.
    – Paul Ramsey
    Jan 13, 2017 at 17:03

1 Answer 1

Reset to default
5

Your LTE geometry I would wager are generated from some kind of raster-to-vector process (such layers usually are) so some of the polygons are probably very complex and large, which results in very long processing times as they hit multiple cities. If you break up the large objects into smaller ones, the spatial indexes can help you out a lot more.

CREATE TABLE lte_subdivided AS 
SELECT ST_Subdivide(geom) as GEOM, gid
FROM lde_coverage;

CREATE TABLE cities_subdivided AS 
SELECT ST_Subdivide(geom) as GEOM, id
FROM cities;

CREATE INDEX cgx ON cities_subdivided USING GIST (geom);
CREATE INDEX lgx on lte_subdivided USING GIST (geom);

SELECT distinct c.id 
FROM cities_subdivided c
JOIN lte_subdivided l 
ON ST_Intersects(c.geom, l.geom)
Improve this answer

Your Answer

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

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