1

I have two large tables with data that spans over multiple regions of the U.S and needs a defined SRID column in atleast one the tables to denote which coordinate system should be used for area/distance calculations:

CREATE TABLE table_a(
  id uuid,
  geom geometry(MultiPolygon,4326)
);
CREATE INDEX ON table_a USING GIST (geom);
CREATE INDEX ON table_a USING GIST (st_transform(geom,2876));
CREATE INDEX ON table_a USING GIST (st_transform(geom,2877));
CREATE INDEX ON table_a USING GIST (st_transform(geom,2926));
CREATE INDEX ON table_a USING GIST (st_transform(geom,...));

CREATE TABLE table_b(
  id uuid,
  geom geometry(MultiPolygon,4326)
);
CREATE INDEX ON table_b USING GIST (geom);
CREATE INDEX ON table_b USING GIST (st_transform(geom,2876));
CREATE INDEX ON table_b USING GIST (st_transform(geom,2877));
CREATE INDEX ON table_b USING GIST (st_transform(geom,2926));
CREATE INDEX ON table_b USING GIST (st_transform(geom,...));

--note there can be multiple applicable SRIDs for a given geometry
CREATE TABLE table_a_srid(
  id uuid,
  table_a_id uuid REFERENCES table_a,
  srid integer
);
CREATE INDEX ON table_a_srid(table_a_id)

The following query is designed to apprehend the closest record of table_b for each record of table_a within a distance of 500ft using the SRID from table_a_srid:

SELECT DISTINCT ON (a.id)

  a.id,
  b.geom

FROM

  table_a a
  JOIN table_a_srid c ON (a.id = c.table_a_id)
  JOIN table_b b ON (st_dwithin(st_transform(a.geom,c.srid),st_transform(b.geom,c.srid),500))

ORDER BY

  a.id, st_distance(st_transform(a.geom.c.srid),st_transform(b.geom,c.srid))

Query plan shows that a sequential scan is used instead of the relevant spatial transformation index for the given SRID -resulting in completely unacceptable performance.

How can I perform this operation efficiently without resorting to UNION ALL for every single SRID defined?

postgis_full_version = "POSTGIS="2.2.0dev r12887" GEOS="3.5.0dev-CAPI-1.9.0 r3995" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.0, released 2014/04/16" LIBXML="2.9.1" LIBJSON="0.11.99" TOPOLOGY RASTER"

  • 1
    Have you considered to cast the whole stuff into geography type? – user30184 Mar 12 '16 at 22:00
  • The query planner doesn't read table_a_srid, so it would never know which SRID functional index it could use. – Mike T Mar 13 '16 at 3:16
  • @user30184 I considered doing this, but my understanding is that using the geography type would result in distance/area calculations that are much less accurate than using a projected coordinate system. If that wasn't the case, it would definitely solve the issue. – THX1138 Mar 13 '16 at 17:25
  • I am not specialist on this area. Do you have some references which suggest that geography type gives inaccurate results? Distances with geography are measured along the great circle or spheroid and I suppose that because of that the results are more accurate at long distances and if use case is for example a travel by car, boat, or airplane. – user30184 Mar 13 '16 at 17:40
2

I think that your index does not work if the geometry is transformed (temporarily) in your join statement. It is in general a bad idea to use the transform function in such an extensive manner since it is computational very expensive. If you know which SRIDs you are going to use, and you know that your table would not get too big, it would be better to create multiple geometry columns a-priori. I am sure that also your index would work than.

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.