Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
7,959
questions
0
votes
0answers
14 views
Points of LinearRing do not form a closed linestring issue for a closed polygon
I can't generate the Minimum Bounding Circle for this polygon:
As you see, it is closed. Why I can't generate the circle?
This is the SQL sentence:
with figure as(
SELECT ST_GeomFromText('POLYGON((0....
0
votes
0answers
31 views
Layer is not valid error when connecting QGIS to PostGIS
I am new to QGIS.
I am trying to connect a view from PostGIS to QGIS (3.16.4, same with 3.12).
The PostGIS connection in QGIS browser have no issues and when "Test Connection" it says it can ...
-1
votes
1answer
27 views
Handling a database with more coordinate points than I need to be returned
I have a database with millions of coordinate points and I need to select a subset of them to create a heat map based on a bounding box. There are so many points that it's hurting performance on my ...
0
votes
1answer
24 views
GeoServer (New Vector Data Source from PostGIS)
I have this error on GeoServer:
Error creating data store, check the parameters. Error message: Unable
to obtain connection: Cannot create PoolableConnectionFactory (The
authentication type 10 is not ...
1
vote
1answer
18 views
How to write docker-compose.yml after enabling postgis_raster for PostGIS Docker Image
I'm using the docker image of https://github.com/postgis/docker-postgis and it works fine with the commands below which is also defined in README file.
docker run --name some-postgis -e ...
1
vote
1answer
20 views
pgrouting/PostGIS Trying to set nearest node to airports in a region brings the same node for each airport
I need to make a distance matrix from all the nodes in the Canary Islands to every airport in the region. I'm following the method set here but it brings the same node for all airports. Trying the ...
0
votes
1answer
44 views
Does QGIS perform better when using a materialized view or a standard view from PostGIS [closed]
My PostGIS views in QGIS are significantly slower then other standard PostGIS tables and I have struggled to enhance their performance any good amount. If using materialized views in QGIS would I ...
1
vote
0answers
11 views
Snap lines (map matched trajectories) to road network in PostGIS
Using OSRM I have map matched a series of GPS points to a custom road network. My goal is to provide insight in where but more importantly how many times the vehicle has visited each road segment in ...
1
vote
0answers
20 views
pgRouting - Assigning source and target from different point layers (AOIs) for network analysis
I'm new to pgrouting and normally use ArcMap to conduct any sort of network analysis. I have created a simple shortest path network along a downloaded OS Mastermap road network using pgRouting. ...
1
vote
1answer
51 views
Uploading 50Gb GIS data (csv files) contained in nested directories into PostGIS database
I have two years worth of GIS data contained in CSV files stored in nested directories (per year, per month) so it looks like:
2013
01
2013-01-01.csv
2013-01-02.csv
etc.
02
...
4
votes
2answers
83 views
Speed difference MS SQL Server 2019 to PostgreSQL/PostGIS 12 for spatial data
I imported a shapefile with 3000 polygons in UTM 25832 into both databases MS SQL Server 2019 and PostgreSQL/PostGIS. Then I calculated a buffer of 1000 meters around these geometries and transferred ...
0
votes
1answer
25 views
QgsVectorLayerExporter writing either geometry column or attribute table, not both
I'm trying to save a WFS layer to a Postgis database with no success. Somehow modifying the connection string I can get QgsVectorLayerExporter to write either the geometry column or the attribute ...
0
votes
0answers
27 views
PostGIS Geography Data Changed After pg_restore
Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using a Docker image kartoza/postgis:13.1.
A dump of the database was ...
7
votes
1answer
288 views
What type is this PostGIS data and how can I get lat, long from it?
I have an R dataframe, with location stored in a column. Location values look like this:
0101000020E6100000797A00E6B5593E40D5CE30B5A5394940
I was told that it is PostGIS data.
The goal is to visualize ...
0
votes
0answers
24 views
How can I see the queries (SQL statements) that are generated when I modify or edit the records of a PostGIS table, inside QGIS? [duplicate]
This is the DB manager, and it shows only the history of the statements that were used, but it does not show the records of the modified data in a table.
How do I see the history of the statements ...
0
votes
0answers
52 views
ogr2ogr TopologyException invalid input geometry; ring self-intersection
I'm facing a problem when using osgeo/gdal:ubuntu-small-3.2.1 docker image and running these commands on the Natural Earth vector dataset:
# Get and unzip the data
# (inspired from https://github.com/...
0
votes
0answers
26 views
Working around ERROR: Unknown geometry type: 11 - MultiCurve
Basically I have a polygon layer and I need to create a view which extracts about 100 different columns from about 10 tables with polygon, line and point geometry, that intersect with the said polygon....
1
vote
0answers
45 views
Creating indexes with PostGIS using all CPU cores
I have to run a whole bunch of GIST index creation, on some tables containing lots of records (scale of many countries, OSM data):
CREATE INDEX CONCURRENTLY IF NOT EXISTS osm_footways_gist ON ...
2
votes
0answers
34 views
Calculating how many points are in each polygon, grouping that polygon by a field in PostGIS
I have two shapefiles: One point file (occ_master) and 1 polygon file (geol_1m). The point file represents mineral occurrences. The polygon file represents geology, which can be subcategorized by ...
1
vote
0answers
35 views
Does QGIS require a specific name for the geometry field in a PostGIS table
I seemed to have noticed that QGIS cannot detect the spatial type or SRID of a PostGIS table if the geometry column is set to anything other than "geom". You can manually tell QGIS what ...
1
vote
0answers
10 views
Using ST_snap to snap pairs of points
I am using the following script to snap a set of point to another within a certain distance. The script works but there are some pairs of points that don't overlap even though the distance between ...
2
votes
1answer
44 views
QGIS “invents” coordinate decimal places from imported PostGIS geom
I have a PostGIS database with an geom-accurancy of 9 decimal places for X and 8 decimal places for Y.
Reading out the coordinates of an PostGIS-Polygon in PyQGIS shows:
import psycopg2
conn = ...
1
vote
1answer
28 views
Upload specific or all tables from Geopackage to PostGIS database
I would like to upload a specific table (i.e. U2018_CLC2012_V2020_20u1 (Multi Polygon)) from a Geopackage file to a PostGIS database from the command line. I tried:
ogr2ogr -f "PostgreSQL" &...
3
votes
1answer
56 views
Intentionally increase coordinates in PostGIS geometry
I have a table with a geometry column (polygon) in a projected coordinate system in meters. Let's say, I want to shift all the polygons by some constant. For example, every point in all polygons must ...
1
vote
0answers
26 views
Query to Display Polygons not Working on Geodjango-Leaftlet Web App
I am currently working on a Geodjango-Leaflet-OpenStreetMap web app. I have around 540000 polygons of property boundaries that were drawn in ArcMap; the polygons will be imported to the web app later ...
1
vote
1answer
38 views
Convert coordinates from EPSG 4326 to EPSG 28992 in PostGIS
I imported a CSV file with coordinates into a PostGIS table. This table contains a column "latitude" and a column "longitude". The coordinates in these columns are in EPSG:4326. ...
1
vote
0answers
27 views
Geometry converts from Polygon to Multipolygon in PostGIS
I'm having difficulties with the Geometry types in PostGIS and/or ArcPy and hope some of you can shed a light on what I'm missing or how I can fix it.
I have a .gdb containing Polygons. The Table in ...
0
votes
0answers
23 views
Adding styles for a raster in PostGIS
I have a PostgreSQL/PostGIS database located on a server.
I have added shapefiles (with the "PostGIS shapefile import/export manager") and raster (with "raster2pgsql"). It works ...
0
votes
1answer
23 views
Create union of buffers in PostGIS visible in QGIS
I would like to merge overlapping buffers created in PostGIS and have as many polygons as there are not overlapping merged buffers, that can be viewed in QGIS.
The answer of this thread seem to be the ...
4
votes
0answers
27 views
Identify U curves in route
I'm trying to identify the mountain roads in a route, and I've thought that a good approach could be get the paths with a very closed curve (like a U turn), but now I can not find any documentation ...
0
votes
1answer
51 views
Which coordinate reference system should I use to have more precision while calculating polygon area? 4326 or convert to the one specific to my area?
We are saving our drawn polygons in our database with the coordinate system 4326.
The polygons drawn are all in Spain (where I came to find out the adequate coordinate system for Spain is EPSG:2062).
...
0
votes
0answers
15 views
Adjusting polygons layers's boundaries in PostGIS [duplicate]
The case is quite typical. I’ve to compare two (multi-)polygons layers of administrative regions, with l2 being lower level (let’s say municipalities – 2811 records) than l1 (let’s say provinces – 785 ...
0
votes
0answers
20 views
How to split lines at every intersection from a single geometry using PostGIS?
The query I tried is this:
Create table test1 as
Select
row_number() over() as ID,
(st_dump(st_split(input.geom, blade.geom))).geom as geom
from roads input
join roads blade on ...
1
vote
1answer
30 views
Deriving x, y (and z) parameters for ST_TileEnvelope from `Geometry` data in table and generating vector files
This is a update 1 thus far on the advice in the comment:
It looks like I need ST_ConvexHull to create the overall bounds of all of the geometries. It's tested against the individual geometries using ...
0
votes
0answers
40 views
ST_Buffer in meters from 4326 point table
I would like to create 100 meters circle buffers in a UTM projection from a point table whose SRID is EPSG:4326 and keeping the id column.
I wonder what is the straightest way. Is that possible to get ...
0
votes
0answers
32 views
Postgis intersection behaves strangely when getting closer to poles
We use Postgis for Django to locate the objects inside a certain viewport. It works great at the equator, but the farther we move, the more distorted the viewport becomes.
Here is the result (the red ...
0
votes
1answer
34 views
QField using QGIS Authentication database?
I run a postgres database with postgis on an external server.
To secure the connection to the database i have self singed certificates set up.
I added the certificates in QGIS and saved them together ...
1
vote
0answers
14 views
How do I append a shapefile to PostGIS table with multiple geometry fields using ogr2ogr?
I am having an issue when trying to append shapefile data to an existing table in my PostGIS database using ogr2ogr. The existing table has multiple geometry fields. When I append the shapefile the ...
0
votes
1answer
13 views
How to get the file location of rasters registered with the PostGIS extension?
Using raster2pgsql I am able to load a NetCDF file into PostgreSQL using the PostGIS 3.1 extension. And looking at the documentation I can see that it's possible to register a raster file instead of ...
0
votes
0answers
23 views
Using ogr2ogr to convert a DXF to GeoJSON and place in PostgreSQL table
Is it possible to use either the ogr2ogr command line to both convert a DXF file to GeoJSON type and put into a PostgreSQL table at one time?
e.g.
ogr2ogr -f "GeoJSON" path/to/output.json ...
-1
votes
0answers
9 views
Integrating documents (PDF / Images / videos / sound)
I am in need of integrating multi-source information on my GIS project. Part of it are normal tables and other content will be images, drawing, videos, PDFs, etc. All tose elements are to be ...
0
votes
1answer
23 views
ERROR: more than one row returned by a subquery used as an expressionSQL state: 21000 Why and how it happens?
I want to update an empty column with a join :
UPDATE icpe_modifie
SET region =
(SELECT nom_reg_m FROM icpe_modifie as icpe
JOIN regions as reg
ON ST_WITHIN(icpe.geom, reg.geom))
icpe_modifie is a ...
0
votes
0answers
34 views
Run ST_DWithin on a subquery result from a different table
I have two tables "Customers", and "Providers" in my PostgreSQL DB. They both have a 'geom' column. Now, customer Table has a column called e.g. "profession".
I want to ...
7
votes
3answers
199 views
PostGIS: Interpolate/Cluster geographic points to average points
Setup: PostGIS 3.1, QGIS 3.16
I have a bunch of geographical data (geometries). They show more or less the same route, just measured several times. Due to the many measurements and the GPS precision, ...
3
votes
2answers
54 views
PostGIS- slow spatial UPDATE queries on 50mil rows
PostgreSQL 11.8, AWS hosted RDS instance, SSD, 8GB RAM, 3.0 GHz Intel Scalable Processor. Plenty of storage space.
I'm finding running updates on a large table using spatial queries is taking a very ...
0
votes
0answers
25 views
Get PostGIS geometry viewer route drawing
I am using pgrouting pgr_dijkstra function to calculate the shortest path between two points. Sorting the result by "seq" gives me the correct sequence of the path to my destination. In the ...
0
votes
0answers
15 views
Trigger converting multilinestrings to curves
I created a small web app based on PostGIS / Geoserver / Leaflet.
I can draw polylines from my interface thanks to the Leaflet Draw plugin and send them to PostGIS with a Php and Ajax script.
When my ...
0
votes
1answer
24 views
Getting multipolygon vertexes to points in PostGIS
I want to extract the coordinates of the vertices of a polygon, and then create a point on each of the vertices.
Sample of Polygon geometry table (multipolygon):
Wished output:
1
vote
1answer
44 views
How to load valid PostgreSQL QgsVectorLayer
Note:
This is NOT a closed question. The following links does NOT provide an
answer to the solution:
Add PostGIS layer to QGIS via Python Console
The Vector layer still remains invalid with the ...
-2
votes
1answer
49 views
GeoJSON coordinates are displaying incorrectly on map
I am setting up my first Leaflet map with react / node / PostgreSQL and have managed to get to the level where I have pulled the geojson form my PostgreSQL database. However from what I can see the ...
