Questions tagged [postgis]

PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

Filter by
Sorted by
Tagged with
0 votes
0 answers
28 views

How to import shapefile to PostgreSQL via PostGIS Shapefile Import/Export Manager?

When I after opened shapefile and click to button Import Log Window gives back error message: dbf file (.dbf) can not be opened. Shapefile import failed. I try to open the dbf file and still repeat ...
user avatar
1 vote
1 answer
64 views

Geometry Viewer in pgAdmin 4 displays a straight line instead of polygons of European union countries

Via PostGIS I import a shapefile layer with polygons of EU countries. I try select all rows and after still display blue straight line without the end. Where is the problem?
user avatar
0 votes
0 answers
15 views

QGIS: Exporting labeling information to PostGIS point layer

In order to comply with a given data model which is exported from a PostGIS schema, I need to export a set of label attributes (object id of labeled object, label text, label position, label ...
user avatar
  • 133
3 votes
0 answers
63 views

Adding a Virtual Layer makes QGIS unresponsive but the test of the query says "No error"

As stated here, I am trying to load a virtual layer from two existing PostGIS tables, using a simple LEFT JOIN statement: SELECT t.id, t.geometry FROM table_t t LEFT JOIN table_f f ON f.fk_poly_id = t....
user avatar
  • 6,748
1 vote
0 answers
28 views

geog GEOGRAPHY(Point) insert creates syntax error [closed]

Why does this return syntax error at or near "5.1963". The documentation clearly states this is how to do it from my understanding? http://postgis.net/workshops/postgis-intro/geography.html#...
user avatar
  • 51
2 votes
1 answer
37 views

Importing single-geometry data to postgis

I have several single-geometry-layers (geopackages, shapefiles, temporary layers). Some are LineString, others are Polygon. When I use the DBManager in QGIS to import these layers to the PostGIS ...
user avatar
  • 1,943
1 vote
0 answers
39 views

PostGIS/PostgreSQL Heavy performance issues with SUM

Not sure if this is specific of GIS stuff or simply a SQL performance issue, but due to the nature of the issue and that I am working with H3 data types here it goes I have a query that runs very ...
user avatar
2 votes
1 answer
65 views

PostGIS function that updates WKT geometries to geometry type with st_geomfromtext [closed]

I come from the Python world but I need to create a PostgreSQLl function that providing a table will: Find columns that have "_wkt", i.e. "geom_wkt" or "geom2_wkt". Do ...
user avatar
0 votes
0 answers
32 views

Add/paste features to PostgreSQL table

I have a PostgreSQL database with several tables. One of them is a non-geometry table that is linked to other geometric tables. I am now trying to copy/paste data from a .csv file to this non-geometry ...
user avatar
  • 1
1 vote
1 answer
57 views

Calculating the distance For each Row with respect to a Common Point

I have two models with no relation Port and a WareHouse each with a lat and long fields. I want to be able to get the distance for each warehouse from the Port and maybe get the closest e.t.c. This is ...
user avatar
  • 113
1 vote
1 answer
27 views

raster2pgsql no more INSERT/COPY statement in the log

I use raster2pgsql every day with Windows command tools, and I'm searching a command to not prompt the INSERT or COPY statement on the terminal because I've got a lot, like an inverse verbose. Is it ...
user avatar
2 votes
0 answers
31 views

Multiple login challenges from PostgreSQL when using QGIS

I am using PostGIS as my main data storage. I have two different geodatabases, one with internal data and one with various background maps. Both those connections are set up with stored user ...
user avatar
  • 2,777
1 vote
1 answer
57 views

PostGIS: get Circle to GeoJSON

CREATE TABLE IF NOT EXISTS public.global_circles ( name character varying(64), geom circle); INSERT INTO global_circles (name, geom) VALUES ('My circle', CIRCLE(POINT(16.56115880637268,46....
user avatar
  • 85
0 votes
0 answers
34 views

Export 3D data from PostGIS to any common open and standardized 3D file format

I am mainly curious, because I found this function: ST_AsX3D() in the list of PostGIS 3.2 "ST_As{something}" functions: st_asbinary, st_asbinary, st_asbinary, st_asbinary, ...
user avatar
  • 6,748
2 votes
2 answers
78 views

undocumented PostGIS ST_Distance function taking text as parameters

It seems that I have a variant of ST_Distance function in my PostGIS (3.2.1) that is not on the documentation CREATE OR REPLACE FUNCTION public.st_distance(text, text) ...
user avatar
  • 10.3k
0 votes
1 answer
253 views

psycopg2 cursor.copy_expert() BadCopyFileFormat: extra data after last expected column when writing WKB data

I am trying to write a WKB representation (i.e. a binary string) of a Shapely Polygon into a PostgreSQL/PostGIS database using psycopg2 cursor.copy_expert() for a bulk insert. I narrowed down the ...
user avatar
  • 6,748
2 votes
0 answers
38 views

DB Manager shows a question mark in front of newly created PostGIS layer

I have a polygon layer in a PostGIS database with 16 polygons for the states of Germany. I want to create a new layer for the state of Saxonia in the east of Germany, which has also a different ...
user avatar
  • 1,943
3 votes
1 answer
74 views

Rounding decimal numbers in PostGIS [duplicate]

I have a 'cities' point layer. I want to compute a table with the coordinates of the geometries which works fine with that expression: select name, ST_X(geom) as x_coordinate, ST_Y(geom) ...
user avatar
  • 1,943
1 vote
0 answers
30 views

PostGIS role limitations overcome via QGIS

I'm using PostgreSQL 14 with PostGIS extension. I've created the following role and grants create role prueba login nocreatedb password 'prueba'; grant select, references, trigger on all tables in ...
user avatar
  • 2,315
2 votes
1 answer
50 views

Should I use different projections to calculate distance and displaying on the Map?

I'm using the UK crime dataset provided by https://data.police.uk/ My question is about coordinate systems/map projections. The crime locations are stored with the projection EPSG 4326 which uses ...
user avatar
0 votes
0 answers
27 views

Fix geometry to run pgr_createTopology which is very slow and fails

I'm trying to create topology in PostgreSQL using pgr_createTopology but it's very slow and fails after processing 4000 nodes. There are 300,000 nodes. I tried the same query with another road table (...
user avatar
6 votes
1 answer
163 views

Converting 1 meter to degrees at specific location using PostGIS

Given a point in EPSG:4326 I need to know how many degrees (approx) 1 meter is at this location using PostGIS. Feels like http://postgis.net/docs/manual-2.1/ST_Line_Interpolate_Point.html is useful ...
user avatar
  • 163
1 vote
0 answers
54 views

Storing a large 3D TIN into a PostGIS database

I have to store a massive TIN (1.5 GB) into a PostGIS database to do some computations with it. After my first try, I was getting a "server closed the connection unexpectedly" issue when ...
user avatar
  • 6,748
3 votes
1 answer
69 views

PostGIS performance using ST_Subdivide [closed]

What is the max or optimal number of vertices for a geometry to improve performance and load time, using ST_Subdivide? What would be the max or optimal shape length? I am trying to find the best ...
user avatar
2 votes
0 answers
43 views

PostGIS ST_Split outputs without a split

I have a PostGIS workflow that has a line file that has been buffered then subdivided and a vectorized raster called myvector. My intention is to split or separate myvector based on the bounds of the ...
user avatar
2 votes
0 answers
223 views

ogr2ogr errors: PROJ: proj_create no database context specified, cannot prase CRS, proj.db lacks database.layout.version.major/minor metadata etc

I have a PostgreSQL/PostGIS database that I have been working on and it's generated in a docker container. I access it via PGAdmin to the docker DB or sometimes I create a backup and restore locally ...
user avatar
4 votes
1 answer
48 views

PostGIS ST_Intersection with interpolated M value

I have a polygon and a GPS Trace (incl timestamps in the M dimension) zig-zagging across the polygon. I want to find the time intervals whenever the GPS trace entered and exited the polygon, which can ...
user avatar
7 votes
3 answers
192 views

Simplification by constrained anti-aliasing in PostGIS

I vectorized a raster data set, therefore, I'm facing a "steps" effect, or aliasing, due to the fact that the vectorization strictly follows the raster cells. So, the geometry is quite large ...
user avatar
  • 6,748
2 votes
1 answer
46 views

Speeding up a TIN to MultiPolygonZ conversion in PostGIS

I have a TIN record in a PostGIS database. It contains between 50'000 and 100'000 triangles: Based on this answer: https://gis.stackexchange.com/a/209845/65370 I'm trying for the moment to dump the ...
user avatar
  • 6,748
2 votes
0 answers
36 views

Get result Bounding Boxes of Spatial Indexing

When I create a Spatial Index in PostGIS, the result looks something like this: https://image.slidesharecdn.com/mongodcknize-120711124945-phpapp01/95/rtree-spatial-indexing-with-mongodb-mongodc-14-728....
user avatar
  • 3,017
0 votes
1 answer
44 views

Saving a TIN given as a Wavefront OBJ file into PostGIS internal TIN format

I was given a TIN representing a terrain as an OBJ file: I need to store it into a PostGIS database as the PostGIS internal TIN format. How could I do that? If needed I can have access to the ...
user avatar
  • 6,748
1 vote
1 answer
43 views

bash: raster2pgsql: command not found

I'm using Debian 11 (bullseye) and have installed postgresql 13.5 and postgis 3.1.1 using synaptic package manager, postgresql-13-postgis-3 As far as I can tell everything is working but when I try ...
user avatar
  • 52.5k
0 votes
1 answer
19 views

PostGIS SFCGAL ERROR: PolyhedralSurface is invalid : inconsistent orientation of PolyhedralSurface detected at edge 0 (0-1) of polygon 3

I am trying to tesselate (yes, in the following case the tessellation is perfectly useless, but it's for the purpose of the example), with the help of ST_Tesselate() from SFCGAL, a polyhedral surface ...
user avatar
  • 6,748
3 votes
1 answer
74 views

What should I do to avoid precision problems with ST_Transform in PostGIS?

Let's say I run the following query on a PostgreSQL database with the PostGIS extension: SELECT ST_AsText((ST_Transform('SRID=4326;POINT(-34.91148366443491 -8.077603972529051)',3857))); The result is:...
user avatar
  • 1,021
2 votes
1 answer
54 views

Getting osm_id from random point with OpenStreetMap database

I've downloaded the OpenStreetMap data from GeoFabrik and imported it into PostgreSQL as described in the first procedures on this page. The import was successful. After that, I found a way of ...
user avatar
  • 1,021
1 vote
1 answer
144 views

Forcing geometry columns to be small on pgAdmin 4

On pgAdmin 4, geometry columns always show as huge lines: So when I want to check the geometries inside Postgres I have to go all the way until the end of the line with a very small scroll bar: That ...
user avatar
  • 1,021
2 votes
1 answer
63 views

ogr2ogr PostGIS to SpatiaLite: convert hstore to json

I used this command to export PostGIS tables to SpatiaLite with ogr2ogr ogr2ogr -f SQLite -progress -dsco SPATIALITE=yes ./mapfile/psql_tags.sqlite PG:"host=localhost port=5432 dbname=gis user=...
user avatar
  • 187
1 vote
0 answers
63 views

How to write Shapely polygons (or a multipolygon) to a PostGIS polyhedralsurface

Context In a Python script, I have a 3D square pyramid composed of 4 triangles (a list of 4 Shapely 3D polygons) that I need to store within a PostgreSQL database (+PostGIS). Here's what this pyramid ...
user avatar
  • 6,748
4 votes
1 answer
104 views

Use ST_DWithin on WGS84 geometry data

I've just used osm2pgsql to import osm data to a PostGIS database, using lat/long option to store it in WGS84 projection. I would like to use ST_DWithin to select nodes close to a random point. When I ...
user avatar
4 votes
1 answer
51 views

Variables in SQL queries with PyQGIS [closed]

I want to create spatial indexes in all the tables that are included in a schema in PostGIS. The layers remain the same but the schema is everytime different. So I wanted to pass a variable var = ...
user avatar
1 vote
0 answers
24 views

Map content not visible in QGIS Cloud web client

I'm using the QGIS Cloud plugin to generate a web map. Suddenly map content is not visible in the web client. When I asked QGIS Cloud for support they are saying there is a problem QGIS authentication ...
user avatar
  • 11
0 votes
0 answers
43 views

PostGIS SummaryStats with many polygons and many bands

I have a hyperspectral image (160 bands) and a polygon shapefile with several thousand polygons. I need to extract mean value of each band within each polygon and store it in a table. I decided to try ...
user avatar
  • 187
1 vote
1 answer
55 views

PostGIS function ST_Difference changes geometry type [duplicate]

I am trying to use the ST_Difference tool within PostGIS. The intention is to take two multipolyogn layers that have some overlap and eliminate the overlap by taking the difference. The problem is ...
user avatar
0 votes
0 answers
46 views

Recovering lost geometry columns in PostGIS

We have been running v10 of PostgreSQL with PostGIS 2.4. We are in the process of migrating to PostgreSQL 14 with PostGIS 3.2. There appeared to be an issue with pg_upgrade and the PostGIS version, so ...
user avatar
  • 1,386
4 votes
0 answers
77 views

Select subset that is spatially equally distributed

Let's say I have a table point with ~10.000 rows. Each row contains a POINT(x, y). I also have a table polygon with POLYGONS(...). The points in the point table are unequally distributed in space. ...
user avatar
  • 3,017
4 votes
3 answers
102 views

Points which are beyond certain distance from multiple points

I have two tables - first is a list of positions (points) and second is a list of cities (cities). I am using PostGIS and can find all points outside 10nm of New York by using the SQL statement - ...
user avatar
2 votes
0 answers
92 views

Dynamically determine origin and destination points on network lines from nearby features using Pg Routing (pgr_dijkstra)

I am trying to determine the distance along a route from parcel to the nearest fire hydrant using pg_routing and pgr_dijkstra. I have a network table and a nodes table and the following query returns ...
user avatar
0 votes
0 answers
26 views

Editing normal PostGIS layer in ArcMap without installing Enterprise Geodatabase

I have an open-source open-protocol product that uses PostGIS layers in PostgreSQL. I have a client now that wants to use ArcMap with this geographical data. I can connect ArcMap to the database as a ...
user avatar
1 vote
1 answer
64 views

QGIS Graphical Modeler PostgreSQL Execute and Load SQL

I have a QGIS graphical model workflow that I would like to be able to use with a connected PostGIS database. I am able to export the layers into the database and associated schema, but when I try ...
user avatar
  • 203
2 votes
0 answers
41 views

raster2pgsql: Argument list too long

trying to insert rasters into my database. In sum, its about 870,000 files. Trying to do so with the following command raster2pgsql -s 4326 -I -C -M -F -l 8,16,32 *.jp2 public.sat| psql -d imagery -h ...
user avatar