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

PostGIS trigger to remove duplicated nodes on update

I have a database with POLYGONs. I want to remove duplicated nodes of the edited/inserted polygon. I made a function: CREATE or REPLACE FUNCTION ov_geometry_cleaner() RETURNS trigger AS $$ Begin ...
0
votes
0answers
10 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 ...
0
votes
0answers
14 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
26 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
30 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
19 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
30 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
32 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" ...
0
votes
0answers
33 views

All intersecting geohashes of a certain length from a polygon in PostGIS

I am trying to find all geohashes of length 5 that intersect a given polygon - like a county boundary for example. The only tool that seems available in postgis is st_geohash. However, Using SELECT ...
3
votes
1answer
55 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
48 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
71 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
2answers
46 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
19 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
36 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
1answer
67 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 ...
0
votes
0answers
10 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)...
1
vote
1answer
36 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
55 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
31 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
21 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
16 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
29 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
30 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 ...
0
votes
0answers
24 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
16 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
56 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
28 views

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

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 ...
2
votes
2answers
121 views

How to create a rule on QGIS to show only features that have last week as date?

Basically I have a PostGIS layer with a date column. I only need to show features that have the last week as a value in the date column. How to achieve this? Unfortunately now() - 1 week doesn't ...
1
vote
1answer
73 views

Installation error - PostGIS on Ubuntu 18 [duplicate]

I have installed PostgreSQL 11 on Ubuntu 18 and while creating PostGIS extension it gives this error. could not load library "/usr/lib/postgresql/11/lib/postgis-2.5.so": /usr/lib/postgresql/11/...
0
votes
1answer
31 views

How do I calculate proximity for multiple points?

I have two layers, one with lots, one with subway stations (NYC). There are to scenarios: Case 1. I want to rank the lots relative to the proximity to subways. (1.a.) The closer to closest station, ...
0
votes
1answer
59 views

Uploading shapefile to PostGIS database using PHP

I want to insert a shapefile into a PostGIS database using PHP. I go through the examples and I tried the following: $command = 'shp2pgsql -s 2100 -d /D:\CHECK DATA-group\2016\Ayomi\JULY\...
3
votes
1answer
88 views

Accessing PostGIS data in QGIS?

I've created a two new PostgreSQL users; one that is supposed to have read-only privileges and another one that should have most privileges (admin-user). When I connect/log in to the new users in QGIS ...
2
votes
2answers
89 views

Why is creating features with FALSE boolean fields in QGIS and Postgres failing when the fields have default values?

I'm having problems with adding rows that have boolean columns to Postgres tables from QGIS. In a nutshell, shapes with boolean fields can only be created when the booleans are set to true (updates ...
4
votes
1answer
125 views

Only union/dissolve intersecting features to save time?

I have one table of about 300k polygons, some intersecting but most do not. I need to dissolve the intersecting ones and also keep the non-intersecting ones. I only need the geometries, no attributes. ...
1
vote
0answers
31 views

Calculate average number of direct neighbors in cities in PostGIS

In need to calculate the average number of direct neighbors in pre-selected cities in PostGIS. So far, I have only found solutions to select neighbors based on distance (e.g. all neighbors within 1km ...
0
votes
1answer
27 views

Find all regions (lat, long, radius) that contain a point

A Region has a latitude, longitude (GEOMETRY('POINT')) and a radius. Using postgis, how can I query all Regions where the distance from pointParam and region.point is less than or equal to region....
1
vote
0answers
37 views

Optimal Voxel Database Schema in PostGIS

I have a database of point data in PostGIS that I'd like to work with as voxel data. For each voxel, the value would be the number of particles within its volume. Let's say for the sake of argument ...
10
votes
2answers
147 views

How to recursively loop through parent polygons intersections to get smallest (child) polygons with no overlaps?

I'm struggling with a problem for a couple of days and realized many people also get stuck when the topic is intersections in PostGIS (v2.5). That's why I decided to ask for a more detailed and ...
2
votes
1answer
33 views

Speeding up ST_Value in PostgreSQL?

I am working on a large point file and am looking to get the raster value underneath each point. I am using: UPDATE Locations a SET image3b3_sum = ST_Value(rast, 1, geom) FROM image_sum_sum; 10 ...
0
votes
0answers
21 views

SQL Query OSM road types in a bounding box

I have a basic command that works to query all planet_osm_line features within a bounding box (running a PostgreSQL db of the entire OSM planet file) SELECT * FROM planet_osm_line WHERE ...
1
vote
2answers
168 views

Migrating US Census GDB file to PostGIS — ERROR: tables can have at most 1600 columns

I'm working with data from the U.S. Census. Specifically, I've downloaded gdb files from here. I have the gdb file ACS_2016_5YR_TRACT.gdb on my server and I'm attempting to create a psql table with ...
-1
votes
0answers
20 views

Migration ArcSDE from MS SQL Server to PostgreSQL/PostGIS [duplicate]

I have ArcGIS Enterprise Geodatabase on MS SQL Server. I want to migrate to PostgreSQL/PostGIS. I have installed and setup PostgreSQL/PostGIS. How do I import that data(with schema) to PostgreSQL/...
1
vote
1answer
23 views

Specify database schema in MapServer mapfile

I have my mapfile. I am able to connect to my PostGIS database when my table is under the public schema. Here is my database structure. Database Ingham \__Schemas (3) \__2016 \...
0
votes
0answers
27 views

heatmap to polygon (only the hotspots)

I have a widgit that makes a heatmap from the proximity of points, I want to create a polygon layer just from the hottest spots(based on color maybe?), for example if the color range is blue to red i ...
0
votes
1answer
24 views

Edit and transform XML/GML files

I'm searching for an approach as someone who just started to work with geodata recently. It's about the following situation: I received several XML files. I need to perform a geometric difference ...
4
votes
3answers
127 views

Using postgis to flatten all polygons in table

I have a table with many polygons. The polygons all have a name and a value. I want to flatten these polygons so that each polygon border remains as border to the resulting polygons. Instead of ...
0
votes
0answers
62 views

Improving performance of PostGIS equivalent to ArcGIS Identity tool?

My goal is to implement ArcGIS's identity tool in PostGIS as part of an effort to port an ArcGIS model builder model to PostGIS on a linux environment. The model by itself only uses "Select Layer By ...
1
vote
1answer
24 views

Why does my source and target repeats itself when I use pgr_createtopology()?

I created my path in QGIS v2.18.18, making the lines and a combination of cleaning tool and explode lines tool to get my desired output. I tried using ROAD GRAPH plugin in order to check whether my ...
2
votes
0answers
30 views

Hosting WMS layer using Geoserver, Tomcat, Amazon AWS?

My goal: provide clients with a password protected WMS url so they can access some of our GIS data. Where I'm at so far: OS: windows 8 I have a polygon shapefile created in QGIS I want to share as ...