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
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 ...

1
2 3 4 5
160