Hey, did you create a gist index on geometry? can you show the query plans? your e-mail also contains confidentiality notice and goes to a public list, fix this into a right way.
вт, 15 нояб. 2016 г. в 21:55, Suraj Birla <[email protected]>: > > > Hi, > > > > We have greenplum database with PostGIS extension. > > > > *Greenplum version :* > > PostgreSQL 8.2.15 (Greenplum Database 4.3.9.0 build 1) on > x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Aug > 8 2016 05:36:26 > > *PostGIS version* > > POSTGIS="2.0.3 r11128" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March > 2012" LIBXML="2.7.8" > > > > I’m working on a POC to find the country/state/country of a transaction > based on the GPS lon and lat. The transaction table has 15-20 millions > records. > > I downloaded the polygon for all countries in a table. > > Added a geometry column to the transaction table and derived the value > using ST_SetSRID(ST_MakePoint(idle_viol_pos_long_deg::double > precision,idle_viol_pos_lat_deg::double precision),4326). > > > > Then started updating the transaction table with country code using the > below statement. > > > > update test a > > set country = su_a3 > > from (select su_a3 ,geom from ne_10m_admin_0_countries) b > > where ST_Intersects(a.geom, b.geom) > > and country is null > > > > Updating 1 million records took around 1 hr. > > > > If I try to update 20 millions , not sure how long it will take. > > > > I tried to reduce the entry in polygon table to 5 as the transaction > received is only from 5 countries.. Didn’t see any gain in the performance. > > Created index on geom column of the transaction table and still no gain. > > > > Question. Whether I’m on the right track? If yes how to overcome the > performance issue? > > Any guidance would be appreciated. > > > > Thanks > > Suraj > =============== CONFIDENTIALITY NOTICE: This e-mail and any attachments > contain information from Lytx, Inc. and/or its affiliates, and are intended > solely for the use of the named recipient(s). This e-mail may contain > confidential information of Lytx and its customers. Any dissemination of > this e-mail by anyone other than an intended recipient is strictly > prohibited. If you are not a named recipient, you are prohibited from any > further viewing of the e-mail or any attachments or from making any use of > the e-mail or attachments. If you believe you have received this e-mail in > error, notify the sender immediately and permanently delete the e-mail, any > attachments, and all copies thereof from any drives or storage media and > destroy any printouts of the e-mail or attachments. > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/postgis-users
