PostGIS – How to Check If a Geometry Column Has Spatial Indexing for Optimized Queries

postgis

I do not have access to DDL / write access of the database. How do I check if existing geometry columns have spatial indexing implemented or not?

Best Answer

You can find a list of all indexes on a table with:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'name' AND schemaname = 'schema';

which will give a result like:

"brownsh_pkey"  "CREATE UNIQUE INDEX brownsh_pkey ON boundaries.brownsh USING btree (ogc_fid)"
"brownsh_geom_idx"  "CREATE INDEX brownsh_geom_idx ON boundaries.brownsh USING gist (wkb_geometry)"
"brownsh_pk"    "CREATE UNIQUE INDEX brownsh_pk ON boundaries.brownsh USING btree (ogc_fid)"

Related Solutions

Fixing PostGIS Column Z Dimension Issue When Geometry Does Not

When there is no implicit cast between two datatypes you need to state it explicitly. In your case ST_Force3D (or ST_Force_3D for PostGIS versions earlier than 2.1) should do the trick:

ALTER TABLE cl 
  ALTER COLUMN geom TYPE geometry(MultiLineStringZ)
    USING ST_Force_3D(geom);

[GIS] Inserting spatial data into existing PostGIS geometry column from shapefile based on ID

I propose to split this task in three steps:

Import the shapefile in a temporary table.
Update the coulumn geom.
Delete the temporary table.

To import the shapefile in a temporary table (e.g. you call it temp) you can use a tool like shp2pgsql. It is included in the PostGIS installation. A tutorial is here avaible.

The following statement will update the geom column of your existing table city_data with the geom from the table temp.

UPDATE city_data a
SET a.geom = b.geom
FROM temp b
WHERE a.city_id = b.city_id;

At the end delete the table temp.

DROP TABLE temp;

Best Answer

Related Solutions

Fixing PostGIS Column Z Dimension Issue When Geometry Does Not

[GIS] Inserting spatial data into existing PostGIS geometry column from shapefile based on ID

Related Question