PostGIS
PostGIS adds geometry and geography types, spatial query operators, and indexes to Postgres. ide99 renders geometries on a map and helps with SRIDs and projections.
Activate
CREATE EXTENSION postgis;
After this, ide99 detects geometry/geography columns and enables the Power Pack.
What's available
In Schema Browser, tables with geometry get:
- Map View — tab with a real map
- SRID converter — UI for changing projections
- Drawing tools — draw geometry with the mouse and get WKT
Map View
Opens in a new tab: right-click a geometry column → Open in map.
Left side — a map (Leaflet with OpenStreetMap tiles by default). Right — a row table. Hover a point — highlights the corresponding row, and vice versa.
Supported types
- Point — marker
- LineString — line
- Polygon — filled area
- MultiPoint, MultiLineString, MultiPolygon — collections
- GeometryCollection — mixed collections
Geometries in non-WGS84 (SRID=4326) projections are transformed on the fly via ST_Transform. For performance on big layers, do ST_AsGeoJSON(ST_Transform(geom, 4326)) in your query.
Styles
- Color by column — color points/areas by another column's value (numeric or enum)
- Size by column — marker size by a numeric column
- Cluster — group nearby markers into clusters (for thousands of points)
Bounds and navigation
- Pan / zoom — usual map gestures
- Fit to data button — pan to fit all points in the result
- Lock to bounds button — after Fit, freeze viewport (no shift on re-render)
SRID converter
Postgres stores geometries in a specific projection (SRID). Common ones:
| SRID |
What |
| 4326 |
WGS84 (lat/lon in degrees) — for GPS |
| 3857 |
Web Mercator — for web maps (Google Maps, OpenStreetMap) |
| 2154 |
Lambert-93 — for France |
| 3035 |
LAEA Europe — for Eurostat |
| 104001 |
EPSG-internal |
Your data could be in any projection. To convert:
Right-click a column → Convert SRID:
On big tables this is expensive — ide99 warns.
Drawing tools
In Map View — toolbar on the left:
- Point — click on the map, get
POINT(lon lat) in WKT
- Line — click multiple points, double-click to close →
LINESTRING(...)
- Polygon — same, for a closed shape
- Rectangle — drag, returns a
POLYGON(...) rectangle
After creating the geometry — Copy as WKT copies it to clipboard. Use right in SQL:
SELECT * FROM places
WHERE ST_Within(
geom,
ST_GeomFromText('POLYGON((37.5 55.6, 37.7 55.6, 37.7 55.8, 37.5 55.8, 37.5 55.6))', 4326)
);
Spatial indexes
Without an index, ST_Within, ST_DWithin, ST_Intersects — Seq Scan. With a GIST index — fast:
CREATE INDEX idx_places_geom
ON places USING gist (geom);
The Health screen section Foreign keys without an index also surfaces spatial columns used in queries without an index.
Tip: typical workflow
- Load data with coordinates (CSV → COPY → table with
lon, lat).
- Add a geometry column:
ALTER TABLE places ADD COLUMN geom geometry(Point, 4326);
UPDATE places SET geom = ST_MakePoint(lon, lat);
CREATE INDEX idx_places_geom ON places USING gist (geom);
- Open Map View — see points on a map.
- Use Drawing → Rectangle for a bbox filter.
Next