Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
8,677
questions
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 ...
- 11
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?
- 11
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 ...
- 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....
- 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#...
- 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 ...
- 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 ...
- 111
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 ...
- 23
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 ...
- 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 ...
- 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 ...
- 23
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 ...
- 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....
- 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,
...
- 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) ...
- 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 ...
- 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 ...
- 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) ...
- 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 ...
- 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 ...
- 21
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 (...
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 ...
- 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 ...
- 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 ...
- 43
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 ...
- 359
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 ...
- 21
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 ...
- 83
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 ...
- 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 ...
- 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....
- 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 ...
- 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 ...
- 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 ...
- 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:...
- 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 ...
- 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 ...
- 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=...
- 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 ...
- 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 ...
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 = ...
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 ...
- 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 ...
- 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 ...
- 359
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 ...
- 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. ...
- 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 -
...
- 133
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 ...
- 880
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 ...
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 ...
- 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 ...
- 61