1

I need to compare millions of geometries by the && operator (BBOX).

When CREATE TABLE see geometry type on PostGIS2, it do also something like INDEX ON t USING GIST(g)? or I need to express, always, the CREATE INDEX in (or after) my CREATE TABLE?

If it is automatic, it is for any field name (or only for "geom")?


PS: (if no index is automatically crated) when I use ST_DWithin(A,B) PostGIS generate a temporary index?

3 Answers 3

2

Generally, database indexes are not created by default.

From Building Indexes of the PostGIS manual,

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] ); 

is required to create a spatial index. It must be based on a geometry column, though what you call that column is up to you.

From CREATE INDEX of the PostgreSQL manual,

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]
( { column | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace ]
[ WHERE predicate ]

is required for creating an "ordinary" (non-spatial) index on a particular column.

You may create more than one index for any table. A rough guide as to when it would help is to look at the queries you use, see what columns are part of the WHERE clauses, and consider indexing those columns.

2

I found an answer to this question a little while back. sped up my code significantly on points, but the same concept applies to other geometry types. you just have to be careful using it with things other than points for unexpected results. Basically, you use the <-> command on geometries instead of st_dwithin. Have a look at this tutorial, it sounds like this is what you need.

http://postgis.net/workshops/postgis-intro/knn.html

1
  • update, use the && operator in your where statement. <-> is for calculating distance between BBOX centers. And as @martin f said, you have to create the index every time.
    – EconGeo
    May 15, 2014 at 8:28
0

No. I exampled the table creation process using the new postgis geometry column constructor like on example below:

CREATE TABLE pois_ny(gid SERIAL PRIMARY KEY , poi_name text, cat varchar(20) , geom geometry(POINT,4326) );

It does create a table with a constrained column named geom which geometry type is POINT and srid 4326. Trying to insert a different geometry will cause a failure.

insert into pois_ny ( poi_name, geom ) values ( 'Teste point', st_makePoint( 0, 0 ) )

The above fails since srid is 0.

But it did not created a spatial index.

4
  • Thanks, good example. What occurs with INSERT INTO pois_ny ( poi_name, geom ) values ( 'A', ST_SetSRID(ST_MakePoint(-70, 40),4326) ) , ( 'B', ST_SetSRID(ST_MakePoint(-71.1, 42.3),4326) ), ( 'C', ST_SetSRID(ST_MakePoint(-70, 40),4326) ) ; when comparing A&&B OR A&&C, it is a "optimized comparison", or no BBOX-index is used? May 14, 2014 at 1:13
  • The explain analyse reported sequencial scan on table. So, no bbox comparison was used. But the test table has few rows of data. Need generate a larger test table with about 5000 rows to see some benefits of index.
    – cavila
    May 14, 2014 at 8:22
  • I'd like to point out that while you can create a table the way you have above, the prefered best practice is to create a general table, and use the sql addGeometryColumn function to add a geometry column, which not only forces you to specify srid, dimensions, and type, but also registers the column with postgis in the geometry columns view in the public schema.
    – EconGeo
    Jun 10, 2014 at 5:48
  • What I understood from manual, that this would be the preferred way with postgis2. The srid is explicit on table, the type is given with the Point parameter. Also, the dimension is declared since POINT is a 2D type. POINTZ would be the 3d type as example. It too did the registration on geometry columns view. All in a small compact two value expression.
    – cavila
    Jun 10, 2014 at 20:00

Your Answer

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

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