18  PostGIS

18.1 Inspect geometry metadata

SELECT
    f_table_schema,
    f_table_name,
    f_geometry_column,
    srid,
    type
FROM geometry_columns;

18.2 Create a spatial index

CREATE INDEX municipalities_geom_idx
ON geoteca_raw.municipalities
USING GIST (geom);

18.3 Spatial intersection

SELECT
    a.id,
    b.municipality_name
FROM analysis.points AS a
JOIN geoteca_raw.municipalities AS b
    ON ST_Intersects(a.geom, b.geom);

18.4 Things to remember

• Confirm that geometries use compatible coordinate systems.
• Spatial indexes improve many spatial queries.
• Validate invalid geometries before complex operations.