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
1
vote
0answers
9 views

Loading features from QGIS project layer to PostGIS table

I am in the process of populating a PostGIS database from a bunch of shapefiles loaded in a QGIS project. The field names of the shapefiles don't match those in the database so it is not possible to ...
1
vote
0answers
8 views

Why pgr_dijkstra function is faster with directed graph?

I'm doing thousands of tests to get the average route time calculated with the pgr_dijkstra function, taking random nodes. In all tests the average time to generate the routes of the directed graphs (...
0
votes
0answers
21 views

How can I optimize this view? It takes more than 30 minutes to accomplish the job

How can I optimize this view? It takes more than 30 minutes to accomplish the job. class Edge(models.Model): geometry = models.LineStringField(null=True) name = models.CharField(max_length=200,...
1
vote
0answers
26 views

PostGIS ST_Transform() fails for many srid on Ubuntu 20.04.2 LTS

I recently installed PostGIS on my Ubuntu machine and ran into an issue transforming many SRID (but not all). For example the first query below fails (ERROR: could not form projection (PJ) from 'srid=...
2
votes
1answer
24 views

PostGIS nest ST_ClusterKMeans on results of ST_ClusterKMeans

I am using ST_ClusterKMeans in PostGIS to perform a spatial clustering. CREATE TABLE kmeans10 AS WITH s AS (SELECT p.*, ST_ClusterKMeans(p.geom, 10) OVER() cid FROM p) SELECT ST_UNION(s.geom) OVER (...
0
votes
0answers
18 views

Is this query going to return clusters within a certain distance of a centerpoint

Essentially I need to be able to show clusters within x meters of a center point. It looks like it is passing the eyeball test but I am guessing I am doing something noobish here. Raw Postgis query ...
2
votes
1answer
42 views

How can I optimize this query PostGIS

I have 2 spatial tables: Streets: linestrings of streets from a country (more than 3 million) Portals: points of adress from a country (around 18 million) I need to move each point to the closest ...
1
vote
1answer
33 views

PostGIS-Geography doesn't show in QGIS after database restore

I recently restored a PostgreSQL database from a SQL dump. It worked well insofar as all my tables and data are restored. However, one of the original tables contained a PostGIS geography column ...
0
votes
0answers
17 views

Extracting start and end points from OSM Segment using QGIS

I'm very new to gis. I have osm layer shape file. How can can I extract start and end points of segments and add points id to the attribute table osm segment?
1
vote
0answers
18 views

Zone statistics with PostGIS Raster

I have a vector layer of polygons (40K) and an elevation raster that I imported into PostGIS. I would like to get the average elevation value for each polygon. First, I imported my raster with my ...
0
votes
0answers
25 views

QField offline working with PostGIS database in a local network

I am using a QGIS project connecting to PostGIS as the database where the network has no access to the internet. When I export the data using QFieldSync, a gpkg file is created in addition to the qgs ...
3
votes
0answers
41 views

Calculate all points in network that are x distance from a starting point in PostGIS with pgrouting

I am trying to write an SQL that calculates all points at a given distance from a point within a network (most likely the wrong term) where the network is stored in a Postgres/PostGIS database as ...
-3
votes
0answers
22 views

How to convert an XML file to .csv [duplicate]

I am trying to convert my file with XML format to CSV to be readable in PostGIS. I want an Python script to convert my file.
0
votes
0answers
20 views

Reading shapefile(zip) without saving it to the drive in C#

I'm currently stuck on this issue, I've tried almost everything but didn't get further than this. What I'm trying to do is read the shapefile and extract the data to save it in my PostGIS database(...
4
votes
2answers
170 views

Why does it matter which geometry I transform in a spatial query?

I am using PostGIS and have two tables. One containing points of start-locations in EPSG:4326 (about 3 million records in total) and another one containing county-polygons in UTM EPSG:25832 (about 30 ...
4
votes
2answers
86 views

PostGIS compute concave hull from points and display in QGIS

I'd like to create a boundary of a set of point using ST_ConcaveHull function in PostGIS My query works fine and I can see the polygon through the geometry viewer embedded in the pgadmin. CREATE TABLE ...
-1
votes
0answers
36 views

Converting XML file into shapefile or PostGIS database [closed]

I am trying to find a Python tool to convert .xml-file to shapefile or to convert it directly from .xml to PostGIS database (I am using old schema editor it export only .xml-file).
2
votes
1answer
27 views

PostGIS: How to join the original polygon name to Voronoi Polygons?

I am trying to replicate this study using the counties (comarques) in my country. https://tjukanov.org/closest-to-me Thus, I'm trying to find which is the closest neighbouring county to any point in ...
1
vote
0answers
35 views

Rendering PostGIS layers in QGIS

I am running QGIS Desktop 3.18.2 on Ubuntu, but I have observed this issue on other operating systems. When I query a relatively small table (~300 simple polygons) from PostGIS on a remote server, it ...
1
vote
1answer
34 views

Is the geometry type of PostGIS represented as WKT or just the Point type?

Despite reading a lot of materials on the subject, including the PostGIS manual I still haven't been able to understand, I'm a beginner in this world of geographic objects. I have two tables in my ...
0
votes
0answers
16 views

Using pgRouting final disconnected nodes from given node

I am new to using the pgRouting library and haven been given a standard edge table id, source, target, cost, reverse_cost I am comfortable with shortest path algorithms, etc, but this has me ...
0
votes
0answers
25 views

Unable to find 'coordinates' in GeoJSON string error when creating controller API

I am trying to create an API to insert Geojson data into Postgres/PostGIS and I am getting the below error message when I try to make a post. Is there anything obvious that stands out where I could be ...
0
votes
0answers
13 views

db manager shapefile import error [duplicate]

I am trying to import a shapefile using db manager to postgresql. My shapefile has OBJECTID (PRIMARY KEY), ID (STRING FIELD eg: 2543-96-7948 )and several other attributes of type string. When ...
2
votes
1answer
54 views

Invalid SRID return from PostGIS ST_Centroid Function

I'd like to get the centroid of a set of polygons with SRID 4326 I start from this simple query to verify the ST_Centroid function CREATE TABLE test AS SELECT ogc_fid, geom, ST_Centroid(geom) ...
1
vote
1answer
19 views

Interlis ili import into PostGIS

I am currently trying to generate an empty database in pgAdmin 4.29 using ili2pg. The Interlis model SIA405_ABWASSER_2015_LV95 is available here. The ili2pg-command I used is java -jar ili2pg-4.4.4....
1
vote
1answer
31 views

Inserting values in a table depending on spatial relation between objects in to tables

I have a new and empty column in a table and I want to insert values, using postgis depending on the spatial relation between the geometries in two tables. I have one table (points) with benches and ...
1
vote
0answers
27 views

In PostGIS, does casting an `ST_Envelope` to a geography take the earth's curvature into account? [duplicate]

Using PostGIS, I have a table of data with geographic points defined as last_known_coordinates GEOGRAPHY(POINT,4326). I want to find all of these within a geographic bounding box with given top left ...
1
vote
1answer
33 views

Assurance that ST_Envelope works with geography as expected

Using PostGIS, I have a table of data with geographic points defined as last_known_coordinates GEOGRAPHY(POINT,4326). I want to find all of these within a geographic bounding box with given top left ...
1
vote
0answers
39 views

PostGIS data storage suggestion for lines with multi-attribute points - Single huge POINTS table or LINES table with geometry field(s)? [closed]

I am currently working my way through PostgreSQL with PostGIS, trying to figure out the optimal way to handle the storage and manipulation of a very large dataset. I have read this and this question ...
1
vote
0answers
13 views

Converting PostgreSQL GeoJSON data into feature services ArcGIS Pro and ArcGIS Online can read

I am having a difficult time converting transforming my GeoJSON data stored in PostgreSQL to feature services ArcGIS Pro can load. I have tried https://github.com/doneill/koop-provider-pg & https:/...
0
votes
0answers
22 views

Users can't save styles on the db PostGIS

I have a PostgreSQL database, and only with the PostgreSQL user (who's the owner of that table), I'm able to save styles to the database. I checked the user's permissions for the other group roles and ...
3
votes
1answer
61 views

How to optimize PostGIS Intersection Query

I've 2 sets of polygons in 2 tables and the sets overlap each other. For each polygon in set A, I get the ID of the polygon in set B that it overlaps the most. This is the query: SELECT DISTINCT ON (a....
0
votes
0answers
23 views

Migrate MySQL spatial DB/tables to PostgreSQL

We have a web system that uses MySQL spatial databases. We want to migrate from MySQL to PostgreSQL. I've been looking for methods to do it. I have found some tools that help you to migrate. However ...
4
votes
2answers
311 views

Closest point on 5km line

Given a point and a line, I am trying to determine the point on the line that is closest to the given point. The given point is always at least fairly close to the line (3-4 meters max) Originally I ...
1
vote
1answer
54 views

PostGIS: How to return polygon nearest the point

I have two tables in a PostgreSQL / PostGIS database 1 polygon and the other point data How do I modify the following query so it returns only the smallest polygon nearest the point The red in the ...
0
votes
0answers
48 views

Using PostGIS operations

I'm new on PostGIS I have this The orange ones are buffers, the red dots are the centroids of each brown polygon So I've tried to cover the brown polygons by making buffers of the centroids (red dots)...
4
votes
1answer
39 views

pgr_dijkstra is considering one-way streets as bidirectional

I imported data from a map extracted from OpenStreetMap. With osm2pgrouting I imported it into PostgreSQL, to be worked with pgRouting and PostGIS. Command used: osm2pgrouting -f DIRECTORY/map.osm -d ...
1
vote
0answers
18 views

Install PostGIS with MacOS Big Sur

I am totally new to either PostGIS or PostgreSQL. I am trying to install PostGIS on my computer, which is running with MacOS Big Sur version 11.2.3 I have installed PostgreSQL 13 with the EDB. Then I ...
0
votes
0answers
27 views

Generate MVT with POSTGIS and feature tolerance

I have a database which has three geometry tables 'tbl_point, tbl_line, tbl_polygon' with millions of features. Each table has two geometry columns named geom & m_geom for geometries and multi-...
3
votes
0answers
44 views

Layer ordering of ST_ASMVT tiles in Leaflet

I am generating MVT tiles for multiple layers in Django using the following query: WITH bounds AS ( SELECT ST_Segmentize(ST_MakeEnvelope(8247861.100083642, 3551570.082242422, 8252753....
2
votes
2answers
64 views

Using ST_Difference [duplicate]

I have 2 tables Table A with this fields (379 rows in this table) The geom of table A contains this (QGIS only used for better visualization) The table B contains this fields (97 rows in this table) ...
1
vote
1answer
37 views

Find country perimeter lengths using PostGIS

I have polygons representing world countries and I am attempting to find the length of their borders in miles I have tried using ST_Perimeter SELECT ST_Perimeter(geom::geography) * 0.000621371 AS ...
0
votes
0answers
40 views

PostGIS - ST_Difference creates extra line segment

I have some line of sights from one point, collected into a multilinestring. I want to cut the multilinestring using PostGIS' ST_Difference and a border-dataset (a single multipolygon). I used this ...
0
votes
0answers
26 views

What geospatial table and indexing functions are suitable for a borehole database design for large volume of serialised constant increment data?

Pretty common use case I suspect, but want to make sure I optimise from the get go. PostGIS I'm suspecting is the weapon of choice, and one way I'll be interrogating data is QGIS. Large part of data ...
-1
votes
0answers
16 views

Build a web mapping secured platform with authentication module for editing layers [duplicate]

I would like to develop an Open Geospatial Data platform using Open Source tools. So far, I started with the data servers to import data layers (PostGreSQL-PostGIS / GeoServer) then I built an ...
1
vote
0answers
29 views

Snap two point layers PostGIS based on a field

I'd like to know if it's possible to snap point to point automatically in QGIS, only when the two layers have the same attribute value in a field. For example, in the first pic they are far away and ...
0
votes
0answers
27 views

raster2pgsql command line issue (Windows 10) - ERROR: syntax error at or near “raster2pgsql”

I'm using the OsGeo4W64 shell that comes with QGIS (I'm using Windows 10) and have managed to log into my postgres database. However I'm looking to import a raster into a new table, and all roads seem ...
1
vote
0answers
10 views

raster2pgsql with the -R flag (remote dataset) results in invalid table

I'm trying to setup a PostGIS table with NED raster tiles stored remotely. My raster2pgsql command works fine and results in a valid table without the -R flag, but when I use the -R flag (for an 'out-...
1
vote
0answers
19 views

QGIS Web View Widget does not load images properly

I have developed a custom slider (html5 + js + css) to be able to add multiple images and files to any inventory item, not only to add it in QGIS but in any Web GIS, corporate webs, etc. The slider is ...
3
votes
1answer
62 views

Why does PostGIS ST_Distance return 0.0?

I have a point and a multipolygon in PostGIS and wish to calculate ST_Distance (and ST_ShortestLine respectively) as follows: SELECT ST_Distance('SRID=25832;POINT(641099.6 5495250.5)'::geometry, 'SRID=...

1
2 3 4 5
162