Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgis]

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

0
votes
1answer
30 views

How to maintain data types in QGIS when importing from PostGIS?

I have data stored in an AWS RDS PostgreSQL database with the PostGIS extension enabled. When I connect to the data from within QGIS (2.18.2) the data types are not preserved. For example a column of ...
3
votes
1answer
83 views

What is the metric of the radius PostGIS query?

I am trying to make a query (using raw query in sequelize orm for a PostgreSQL with PostGIS database) for a radius of a certain 'maxDistance' radius (preferably in meters): const maxDistance = ...
0
votes
0answers
17 views

Reading multiple rasters (rows) from a single PostGIS table using the PostGIS raster driver in R

I started with: How to load postgis raster layers into R? but ran into some problems. First, my data is stored with multiple rasters (time series precipitation data) each as a row in a single table....
-2
votes
1answer
35 views

Select the parts of a line that are within an area in PostGIS [closed]

In my project I have a road network and many administrative boundaries. My goal is to select the roads that intersect a concrete district but extracting only the parts of the roads that are within ...
1
vote
1answer
24 views

Transform CRS for all geometry objects in SQL-Database in one step [closed]

I would like to easily change the CRS for all my geometry objects in a PostGIS/SpaitaLite/GPKG database in one step. In my case, all objects are in EPSG:4326 and I want them in EPSG:3044
2
votes
2answers
42 views

PostGIS MakeLine issue

I am using GPS coordinates and I am trying to create lines (routes) from lon,lat. I create a geography column from my point and then I create the line as following: ST_MakeLine(point_geom order by ...
1
vote
2answers
53 views

Converting trajectory to outer edges polygon

Using PostGIS, I'm trying to solve the following problem: I have trajectory data (coordinates from API query), like on this image: and want to convert it into polygon, having only outer edges:
2
votes
1answer
27 views

osm2pgrouting creates erroneous roads

osm2pgrouting works without giving any errors but most of the roads have lost their geometry. Result is ; What I've done so far; OSM Data downloaded from http://download.geofabrik.de/ in pbf ...
1
vote
1answer
44 views

How to set SRS to 'EPSG:4326' in geotools?

I have the following code, referenced from my previous post: https://stackoverflow.com/questions/52554587/add-new-column-attribute-to-the-shapefile-and-save-it-to-database-using-geotools. So what I am ...
1
vote
1answer
27 views

Run query between PostGIS layer and Shapefile layer with DB Manager

Is it possible to use the DB Manager in QGIS (I am using version 3.4) to run a SQL query between a project layer (e.g. shapefile) and a PostGIS layer? For example, I have a polygon shapefile in my ...
0
votes
0answers
72 views

PostGIS ST_Within is super slow

I am interested in creating a filter polygon to get all the signals that are a part of that polygon alone. For this I created a vector layer QGIS 3 and drew a polygon around the area I want to filter-...
1
vote
1answer
35 views

How can I merge two geometry columns of different tables to a single column in another table

I have many tables with geometry attribute which is saved in Postgis database. These tables are basically from more than one shapefiles which is stored in Postgis using geotools. Now I want a common ...
2
votes
1answer
36 views

Polygon to Multipolygon

I have a database in PostgreSQL / PostGIS with polygons so how can i merge some polygons into multipolygons of the database using it as QGIS Layer?
1
vote
1answer
23 views

Tracing Intermittently Working in QGIS

I'm trying to cut/create polygons in QGIS 3.2.1 using snapping/tracing, but it only works intermittently - it will follow an existing vector line quite happily for a short distance, then drop it, then ...
0
votes
2answers
27 views

Why do the following PostGIS operators perform so differently? [duplicate]

I encountered large performance differences for spatial intersections in PostGIS depending on the operator I'm using and I can't figure out what's causing them. Background: I have a large set of ...
3
votes
2answers
115 views

ST_ConcaveHull interaction with ST_Boundary

I am trying to run a st_concavehull after st_boundary/st_exteriorring on polygons, however st_concavehull doesnt seem to reinclose the polygon properly. For example SELECT ST_GeomFromText('POLYGON((-...
1
vote
1answer
59 views

Tile boundary issue with ST_AsMVT

I have been following a post here to create vector tiles direct from my database. My data is in WebMercator. I am using the query: SELECT ST_AsMVT(tile) FROM (SELECT dh_id, ...
0
votes
1answer
50 views

PostGIS: calculate minimum distance between feature types

I have polygon layer in QGIS, and for each polygon I would like to calculate the minimum distance to the nearest polygon in another layer. My desired result is an attribute for all features that has ...
1
vote
2answers
79 views

Looping through a table to allocate kids to nearest school

The general problem is to allocate town's kids to their nearest school and get the distance and the geometry. Tools in use is Postgresql 10.6, PostGIS 2.4.3 and PGRouting 2.5.2 There are three ...
2
votes
2answers
95 views

Renaming duplicates in PostGIS?

I have a table with a few columns (id, col2, col3, geom) in a PostgreSQL/PostGIS database. I want to find all duplicates (or more) from "col3" having that they share value in "col2". The next step ...
0
votes
0answers
55 views

Edit a view of a PostGIS table in QGIS

I have a simple layer in PostGIS created as follows: CREATE TABLE layer ( id serial PRIMARY KEY, geom geometry(LINESTRING, 31370), ... ) To avoid duplicate information, I created a view ...
0
votes
1answer
49 views

gdal_rasterize -te and -tr/-ts options

I'm using gdal_rasterize function to create a raster from my PostGIS database which contains measurements over the entire earth of a gas concentration. (almost 600'000 points over the polygon below) ...
2
votes
1answer
63 views

PostGIS topology hierarchal topogeometries

I'm seeking guidance on how to correctly organise hierarchal topogeometries. I'm working through PostGIS in Action's chapter on topology and, in summary, it sets up two tables with topogeometries, ...
2
votes
0answers
37 views

Parallel optimization for query over LiDAR dataset

I'm using PostGIS + pointcloud to store a huge dataset of LiDAR point (2M patches of 600points) I query this database to obtain the altimetic profile between two points. At the moment I have a huge ...
0
votes
1answer
62 views

Using contextual information to plan route?

I downloaded the .osm map data, and I imported it to postgresql using the m2pgrouting. I have a CSV file on violence data, with three attributes, nature of occurrence, latitude and longitude. How do ...
2
votes
1answer
132 views

Using ogr2ogr to convert multiple GML files to a postgis table

I have a folder with 150 .gml (addressbase) files I need to upload to my postGIS/postgres database. After the first gml is uploaded, the rest will be appended to it to form a single table. However I ...
1
vote
0answers
36 views

Import ArcSDE db to Postgres/PostGIS

Is it possible to import an ArcSDE db in PostGIS? I don't have experience with ArcSDE and then I don't have any idea to what is the data exportation format of this software. A customer asked me to ...
2
votes
1answer
84 views

Slow PostGIS line intersection even with indexes

I have line data contained in different graphs. Querying data over multiple graphs and using st_intersects results in slow queries. All lines are represented as geometry(LineString). I am querying ...
1
vote
0answers
31 views

translate point based on the point side attribute and link direction

I have address points with house number and their side. For instance, there is a point of house number 10, and it's side is Left (whether Right or Left) I also have the link that has relationship ...
1
vote
0answers
41 views

QGIS/PostGIS: geom as Point in -2147483648

Based on QGIS 3.4 and PostGIS 2.5: I imported a shapefile in my PostGIS database using shp2pgsql. When I connect to my PostGIS database, I see that 2 tables were imported. One with the hint "geom as ...
0
votes
1answer
33 views

How to use a secondary geometry in LABEL object

I have a set of settlement features with two geometries: one is an outline polygon and another is a label point. The features are fetched from a PostGIS db: LAYER NAME "settlements" ...
3
votes
1answer
87 views

Spatial Join In PostGIS where centroid of a polygon is inside another polygon

I have two overlapping polygon layers as shown in figure below. One with small grid polygons and another with a large irregular polygon. I want to join them such that attributes of the large polygon ...
3
votes
1answer
51 views

How to flatten spatially equal polygons in PostGIS?

I have the following dataset of polygons. All the pieces are broken based on their borders. I added some transparency to better understand what's going on. The pieces that have stronger colors, means ...
1
vote
1answer
106 views

PostGIS Symmetrical Difference

Related question I have two tables in PostGIS, each with two features(rows) and a rectangular geometry (polygon). I would like to obtain the Symmetrical Difference from those two layers so that the ...
2
votes
3answers
75 views

Seeking PostGIS equivalent to GeoPandas union overlay?

What is the PostGIS / PostgreSQL equivalent of the GeoPandas Overlay Union operation? res_union = geopandas.overlay(df1, df2, how='union') Say I have two tables, 1 green, 1 red with each with two ...
1
vote
0answers
59 views

Plugin for MongoDB in QGIS

I'm developing an application for a client based on Vue and NodeJS. Earlier MS Access database was used and I have migrated it to MongoDB and also to PostgreSQL in case I don't find any plugin for ...
1
vote
1answer
41 views

Incorrect extent when importing shapefile to PostGIS

Using PostgreSQL 9.6, pgAdmin 4 v3.5 and QGIS I’ve found quite a bit of info on this issue but no clear answer. I’m attempting to import a shapefile that was created using ArcGIS (don’t know what ...
1
vote
2answers
99 views

Combining two spatial tables in PostGIS

I have two PostGIS tables with geometries. One with number attributes and one with letter attributes. Each table has two features (rows). I would like to apply a operation on both stables so that I ...
1
vote
0answers
14 views

Extract ordered point coordinates from polygon layer via Virtual Layer or PostGIS View

I have polygon layer and I need to create table with XY coordinates for each polygon staring from farthest to west (lowest value of X). Now I have Virtual Layer with SELECT ST_DissolvePoints(geometry)...
2
votes
1answer
50 views

finding isolated rings in postgis

I want to find the isolated rings in an OSM dump loaded into PostGIS, using pgRouting. I need to do this in order to delete the isolated rings of the network. Does anyone know how to do it?
3
votes
1answer
93 views

Automatically update PostGIS layer in QGIS

Is there a way to update a PostGIS layer in QGIS (every 5 minutes for example) without needing to right click on the layer and going through the Update SQL layer dialogue as shown below? The end goal ...
0
votes
1answer
76 views

Spatial join attributes based on closest point in PostGIS

I have two point tables from which I want to extract a value from one table to the other, based on the nearest distance of those two points. t1 t2 id | value id 1 | 100 1 ...
1
vote
1answer
26 views

Cross-schema function call with PostGIS dependencies

I have an issue where I am unable to make a cross-schema function call because of that function's use of PostGIS. I have both PostGIS and my function in the public schema, but I'm trying to call the ...
0
votes
0answers
17 views

pgr_astar function changing the heuristic function

I'm using pgr_astar function of PostgreSQL, I'm aiming to find the most fuel efficient path. So, I precises the cost of fuel and the reverse cost of each road segement. The problem that the heuristic ...
0
votes
1answer
33 views

PostGIS Union of two polygons layers

With PostGIS, I need to perform the union of two polygon layers that have intersection areas and distinct areas while preserving all limits. I need to extract separated polygons for each intersected ...
1
vote
1answer
55 views

Image service with GeoServer, PostGIS

I haven't setup or deployed any web services and most help seems to be around vector data... I need input on how to set up and optimize an image service with GeoServer, PostGIS to use in OpenLayers ...
-1
votes
0answers
28 views

How to display on PostgreSQL the shapefile metadata

I'm a beginner on PostgreSQL/PostGIS. I'm working in a project using a spatial data base which I'm trying to display, on PostgreSQL, the shapefile metadata of each file. As every metadata is on ...
1
vote
0answers
46 views

Geohash length to be reversible to LatLong using PostGIS

Decode/encode Geohashes are easy, and there are a "rule of the thumb" for estimat mininal Geohashe length for each LatLong precision. The question is other: precision for revesibility, How many ...
0
votes
2answers
75 views

Checking for overlapping geometries in one table using PostGIS?

I have a table with geometries where some geometries overlaps with other geometries in the same table. I already found an answer for this question here: Check whether table has overlapping polygons, ...
1
vote
1answer
31 views

How should variable data related to a point be stored in a database table? [closed]

I've been having a bit of trouble understanding how a dataset is built/managed for GIS applications. Over the past few weeks (part time) I've been trying to understand the basics of GIS, and how it ...