CODEWITHSUNDEEP
postgresqloptimizationgispostgis
learn_more
learn_more

Reputation: 197

ST_contains taking too much time

I am trying to match latitude/longitude to a particular neighbor location using below query

create table address_classification as (
select distinct buildingid,street,city,state,neighborhood,borough
from master_data
join 
Borough_GEOM
on st_contains(st_astext(geom),coordinates) = 'true'
);

In this, coordinates is of below format

ST_GeometryFromText('POINT('||longitude||' '||latitude||')') as coordinates

and geom is of column type geometry.
i have already created indexes as below 

CREATE INDEX coordinates_gix ON master_data USING GIST (coordinates);
CREATE INDEX boro_geom_indx ON Borough_GEOM USING gist(geom);

I have almost 3 million records in the main table and 200 geometric information in the GEOM table. Explain analyze of the query is taking so much time (2 hrs). Please let me know, how can i optimize this query.

Thanks in advance.

Upvotes: 1

Views: 978

Answers (1)

Evan Carroll
Evan Carroll

Reputation: 1

  • As mentioned in the comments, don't use ST_AsText(): that doesn't belong there. It's casting the geom to text, and then going back to geom. But, more importantly, that process is likely to fumble the index.
  • If you're unique on only column then use DISTINCT ON, no need to compare the others.
  • If you're unique on the ID column and your only joining to add selectivity then consider using EXISTS. Do any of these columns come from the borough_GEOM other than geom?

I'd start with something like this,

CREATE TABLE address_classification AS 
  SELECT DISTINCT ON (buildingid),
    buildingid,
    street,
    city,
    state,
    neighborhood,
    borough
  FROM master_data
  JOIN borough_GEOM
    ON ST_Contains(geom,coordinates);

Upvotes: 3

Related Questions