As of May 31, 2023, we have updated our Code of Conduct.

Hot answers tagged

98 votes
Accepted

What is the purpose of PostGIS on PostgreSQL?

If you re-wound the universe to early 2001, and not only let the inventors of PostGIS see the future, but also let the PSC of PgSQL see the future, perhaps PostGIS would be a series of patches on ...
Paul Ramsey's user avatar
  • 19.7k
92 votes

SQL query to have a complete geojson feature from PostGIS?

This can be done a bit more simply with json_build_object in PostgreSQL 9.4+, which lets you build up a JSON by supplying alternating key/value arguments. For example: SELECT json_build_object( '...
dbaston's user avatar
  • 12.8k
72 votes

Creating geometry from lat/lon in table using PostGIS?

Newer versions of PostGIS allow the following, slightly more common, syntax: ALTER TABLE your_table ADD COLUMN geom geometry(Point, 4326); Then use ST_SetSrid and ST_MakePoint to populate the column: ...
anneb's user avatar
  • 1,281
58 votes
Accepted

Setting up indexes for PostGIS distance queries

There are two keys to getting good geodetic query performance with large tables with geometry columns using WGS 1984 geographic data (SRID 4326): Use the ST_DWithin function, which searches using an ...
Vince's user avatar
  • 19.7k
42 votes
Accepted

Storing GeoJSON FeatureCollection to PostgreSQL with PostGIS?

Assuming you have at least PostgreSQL version 9.3, you can use a few JSON functions and operators to extract the relevant parts of the GeoJSON specification required by ST_GeomFromGeoJSON to create ...
Mike T's user avatar
  • 41.3k
40 votes
Accepted

Postgis change geometry type to Polygon

You can change the Column Type to Polygon with this command: ALTER TABLE oak_all_buffers ALTER COLUMN geom type geometry(Polygon, 102689); or change to MultiPolygon if you have mixed Polygon/...
pLumo's user avatar
  • 6,329
40 votes

Adding GeoPandas Dataframe to PostGIS table?

Using Panda's to_sql method and SQLAlchemy you can store a dataframe in Postgres. And since you're storing a Geodataframe, GeoAlchemy will handle the geom column for you. Here's a code sample: # ...
Hamri Said's user avatar
34 votes

What is the purpose of PostGIS on PostgreSQL?

That's simply not true, PostgreSQL does not support Spatial data types. It supports geometric types. These are perfectly fine for some things, but they're totally separate from real world coordinate ...
Evan Carroll's user avatar
  • 6,941
33 votes

Import GeoJSON into PostGIS

You can use PostGIS's ST_GeomFromGeoJSON to bring in just the geometry part of the GeoJSON. Better yet, you can use ogr2ogr to import the entire JSON document: ogr2ogr -f "PostgreSQL" PG:"dbname=...
alphabetasoup's user avatar
32 votes

Exporting Feature GeoJSON from PostGIS?

Just to provide info on an old question if someone encounters this: as of PostgreSQL 9.5+ SELECT jsonb_build_object( 'type', 'FeatureCollection', 'features', jsonb_agg(feature) ) FROM ( ...
Philip Attisano's user avatar
32 votes
Accepted

How to display vertex markers in QGIS for non-editable layers?

No need to create a new layer. You can show your layer's vertices using the Outline: Marker line Symbol layer type. Just add a new symbol layer Add a new symbol layer; Choose Outline Marker line (...
Alexandre Neto's user avatar
28 votes

PostGIS Geometry type not matching column type in QGIS

I use ogr2ogr to automate ingest of shapefiles into a PostGIS database. Specifically with regard to the question, use the option: -nlt PROMOTE_TO_MULTI This will force ogr2ogr to promote POLYGON ...
Rob's user avatar
  • 606
28 votes

Reading postgis geometry with shapely

The default format for PostGIS geometry is hex-encoded WKB (Well-Known Binary). Shapely has the ability to convert this format into shapely geometry object with its wkb module: from shapely import ...
Grant Humphries's user avatar
28 votes
Accepted

Adding additional geometry column in PostGIS?

To add a column to an existing table, use the ALTER TABLE DDL, e.g.: ALTER TABLE my_table ADD COLUMN the_geom_mercator geometry(Geometry,3857); which can be populated from another column (...
Mike T's user avatar
  • 41.3k
28 votes
Accepted

Setting up PostGIS database and opening it in QGIS on Windows?

It sounds as if your administrator has installed the database successfully and provided you with the details you will need. They may have also configured PostGIS during the install as well, but if not,...
Martin Hügi's user avatar
  • 3,532
28 votes
Accepted

Uploading Geopackage contents to PostgreSQL

This tutorial shows how to import geopackges into postgres with ogr2ogr like: ogr2ogr -f PostgreSQL "PG:dbname=gadm" DNK_adm.gpkg Note importing into PostgreSQL like this will convert the case of ...
Mazu_R's user avatar
  • 1,062
26 votes

Getting geometry length in meters in PostGIS

It looks like since PostGIS 2.0 (maybe before) you can also just do this with ST_Length by recasting the geometry to geography: ST_Length(geom::geography) For example, the distance between 50°N, 12°E ...
andybega's user avatar
  • 507
26 votes
Accepted

PostGIS - how to effeciently ST_Union all overlapping polygons in a single table

The simplest way would be to ST_Union the entire table: SELECT ST_Union(geom) FROM tmpTable; This will give you one huge MultiPolygon, which probably isn't what you want. You can get the individual ...
dbaston's user avatar
  • 12.8k
26 votes
Accepted

Converting geoalchemy2.elements.WKBElement to WKT

GeoAlchemy supports the PostGIS functions ST_AsText and ST_AsGeoJSON. You can use these in your query to return a WKT/GeoJSON string from the database instead of the WKBElement itself. from ...
Ali's user avatar
  • 3,965
25 votes
Accepted

Check whether table has overlapping polygons, in PostGIS?

In the situation where you only need to know whether a table contains any overlapping polygons, and you're not concerned with the size or abundance of overlaps, I recommend a query of the following ...
dbaston's user avatar
  • 12.8k
24 votes
Accepted

GeoDjango: Find all points within radius?

from django.contrib.gis.geos import Point from django.contrib.gis.measure import Distance lat = 52.5 lng = 1.0 radius = 10 point = Point(lng, lat) Place.objects.filter(location__distance_lt=(...
Alexander's user avatar
  • 938
24 votes
Accepted

Understanding the format: WKB from WKT and how to convert the first into the latter

Generally speaking, this is called hex-encoded WKB. This specific example is the extended version, called EWKB, since it has SRID=4326 as found by E6100000. WKB can be viewed in a few forms. The hex-...
Mike T's user avatar
  • 41.3k
24 votes

In PostGIS: how to split linestrings into their individual segments?

You are going about it the right way, using ST_PointN and generate_series. One way of doing this, using dummy data (substitute your own in initial CTE) would be: WITH sample(geom, id) AS (...
John Powell's user avatar
  • 13.5k
23 votes

Creating geometry from lat/lon in table using PostGIS?

If you haven't already add a geometry column (assuming EPSG:4326 here): SELECT AddGeometryColumn ('your_table','geom',4326,'POINT',2); Then UPDATE, passing in the names of your x and y fields: UPDATE ...
toms's user avatar
  • 6,928
23 votes
Accepted

ogr2ogr error importing shapefile into PostGIS : "numeric field overflow"

For making the conversion to success read the manual page http://www.gdal.org/drv_pg.html from section Layer Creation Options PRECISION: This may be "YES" to force new fields created on this layer ...
user30184's user avatar
  • 60.3k
23 votes
Accepted

Is it ok to group by geometry in PostGIS?

There's almost certainly nothing wrong with grouping by geometry in this case, since you're already grouping by a unique ID (nbhd_id). And, as you point out, it saves you a join and makes the query ...
dbaston's user avatar
  • 12.8k
22 votes

PostGIS nearest points with ST_Distance, kNN

This can be done with a LATERAL JOIN in PostgreSQL 9.3+: SELECT signs.id, closest_port.id, closest_port.dist FROM traffic_signs CROSS JOIN LATERAL (SELECT id, ST_Distance(ports....
dbaston's user avatar
  • 12.8k
22 votes
Accepted

Using ST_Difference to remove overlapping features?

A self-join allows you to operate on the relationship between pairs of two features. But I don't think you're interested in pairs: for each feature, you want to operate on the relationship between ...
dbaston's user avatar
  • 12.8k
22 votes

Cleaning geometries in PostGIS?

You can try ST_CollectionExtract to extract [Multi]Polygons from GeometryCollections. Use ST_Multi to force them as MuliPolygons. UPDATE public.valid_lcmsouthshapefile SET geom=ST_Multi(...
Mike T's user avatar
  • 41.3k
22 votes
Accepted

postgis update multipolygon with st_makevalid() gives error

ST_CollectionExtract will pull out a particular type of component from a GeometryCollection. For example, ST_CollectionExtract(geom, 3) will return a Polygon or MultiPolygon. Since your column type ...
dbaston's user avatar
  • 12.8k

Only top scored, non community-wiki answers of a minimum length are eligible