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
7
votes
1answer
171 views

Finding opposite side of a polygon

I have a polygon and known 2 points. One is the center of the polygon and another is outside the polygon. I'm trying to find the opposite point of the polygon where the line string of the 2 known ...
1
vote
1answer
42 views

Find Largest Building in each Parcel (largest feature in each groupby set)

I have a question similar to this one, but the proposed approach doesn't seem to work. For context, I am new to qgis. Given a set of parcels, each with 0 or more buildings, I want to select only the ...
1
vote
1answer
50 views

How to keep feature ID within GeoJSON ogr2ogr import to PostGIS

I'm attempting to import a GeoJSON file into PostGIS using ogr2ogr The basic command is ogr2ogr -f "PostgreSQL" PG:"dbname=my_db user=postgres" my_file.geojson -preserve_fid -nln ...
3
votes
2answers
151 views

Find polygons that has no points in PostGIS

I have a layer with points and a layer of polygons. I can find all the polygons that contains at least one point inside itself by: SELECT polygons.id FROM polygons, points WHERE ST_Intersects(polygons....
0
votes
0answers
47 views

ogr2ogr PostGIS to GeoJSON using batch file

I am trying to export a PostGIS table to GeoJSON using ogr2ogr. The following works perfectly when I launch it in Windows command : ogr2ogr -f "GeoJSON" F:\output.json PG:"host=**** ...
1
vote
1answer
75 views

GeoServer+PostGIS performance for very detailed data

I have a layer on GeoServer, taking very detailed data from a PostGIS table. I can see the size of the table is quite big Size 977MB, External Size 977Mb becoming the performance really low. GIST ...
3
votes
2answers
70 views

PostGIS - identify duplicate points from polygons/linestrings?

I'm trying to identify polygons/linestrings with duplicate points, using PostGIS. I just want to tell whether the geometry has duplicate points, not remove the extra point. I know there is ...
0
votes
0answers
83 views

Problem with massive data importation to PostGIS/PostgreSQL

I need to import a huge SIG data into PostgreSQL (my shapefile table have almost 1.700.000 rowns and 43 columns). I'm using PostgreSQL 12.4 and PostGIS 3.0.x, that came together with PostGIS Bundle 3 ...
1
vote
2answers
82 views

Non-noded intersection issues from overlapping buffer objects

I am working with valid lat/lon point data, creating buffers, and trying to calculate overlapping areas from buffers and running into the not-so-rare error with non-noded intersections when using ...
2
votes
1answer
33 views

Is there a simple table structure update procedure for a manipulated PostGIS layer under QGIS

I have an intensive data exploring session using Postgresql/PostGIS as a backend and try to explore the results using QGIS. Within QGIS I have a defined a "complex" style to show the results....
1
vote
0answers
32 views

Determine if two geometries are similar in shape and location

Suppose I have two geometries that have almost identical location and shape. I say almost on purpose here, since one or the other might have an extra node, or the order of coordinates in GeoJSON might ...
1
vote
1answer
36 views

PostGIS combine ST_DistanceSphere with Nearest Neighbor search

I have two tables both have point geometry with SRID 4326 (WGS84) I need to find the nearest point in 1 table to every point in another table PostGIS KNN search is written t1.geometry <-> t2....
3
votes
3answers
61 views

Calculating value of unique points in polygon in PostGIS

I'm working with a spatial PostgreSQL database that contains species records, there is a point layer 'spp_points', each point has a "spp_ID" and "spp_score" field. I also have a ...
1
vote
1answer
67 views

shp2pgsql: not found on Debian 10 PostgreSQL 13

I have installed PostgreSQL 13 by using the following repository deb http://apt.postgresql.org/pub/repos/apt buster-pgdg main and I have installed PostGIS using apt install postgresql-13-postgis-3 ...
1
vote
1answer
55 views

PostGIS ST_AsGeoJSON throws error

I'm inserting Open Street Map data into a PostGIS database using the GeometryBuilder of osm4j. I then fetch the data and display it on a Leaflet map using ST_AsGeoJSON. This usually works flawlessly, ...
0
votes
1answer
57 views

User-friendly maintenance of the PostGIS geodatabase [closed]

We have a PostGIS geodatabase that is populated by several people. The base is gradually growing, and it becomes unclear to users what data we have and how to find it. In fact, only the person who ...
1
vote
0answers
81 views

Join the result of an SQL query from PostgreSQL to QGIS

I want to send an SQL query from a Python script in QGIS to PostgreSQL/PostGIS and get back the result as a table which I then join in QGIS to a shapefile. How do I do that? At the moment I try to ...
1
vote
1answer
50 views

Approach to Geographic Location Optimization in PostGIS

My goal is to take a geocoded list of targeted consumers, a shapefile of "regions", and create an optimized number of "district" shapes (a geographic child to or subregion of ...
1
vote
0answers
42 views

Maintaining exact geometries with st_subdivide of PostGIS [closed]

I have noticed that when I use st_subdivide() function in PostGIS, the resulting area of the polygons can be different than the input. Is there anything I can do to avoid this happening?
1
vote
1answer
32 views

Can QGIS load a georeferenced (skewed) raster from PostGIS database?

Summary I can't load a georeferenced/skewed raster in QGIS if it is stored in a PostGIS database. Problem Details For some reasons, I need to create a skewed raster in a PostGIS database, and use it ...
3
votes
2answers
221 views

PostGIS functions for generating centerline from polygon

I want to create a centerline from a polygon and I have tried using both ST_StraightSkeleton and ST_ApproximateMedialAxis functions in PostGIS. It gives an error with a message straight skeleton of ...
1
vote
1answer
29 views

Use Common Table Expressions with pgr_TSP

I have a query that uses a handful of CTEs to return a generated set of points. The actual query is more complicated, but the following will do as an example: WITH geom AS ( SELECT unnest('{ ...
0
votes
1answer
70 views

PostGIS via GeoDjango: function ST_DistanceSphere(geometry, geometry) does not exist

I've been using PostGIS via GeoDjango (Django v3.0.7), happily until just now. I have a query (Django .filter()) that looks like this: .filter(point__distance_lte=(dlPt, D(m=CloseRadius))) when it ...
1
vote
0answers
18 views

How to verify that all dependency libraries for PostGIS are installed correctly and working correctly? [closed]

I am attempting yet again to build PostGIS from the source to enable the fabled address-standardizer and the reverse-geocoder plugins for PostgreSQL. I have carefully discovered the source ...
5
votes
0answers
53 views

Does QGIS use data source-specific operations?

This may be a very basic question, but I couldn't find an appropriate answer. Does QGIS use data source specific operations where available and appropriate? For example, when using the Join attributes ...
7
votes
2answers
279 views

Exporting shapefile into PostGIS via Python script from QGIS

I want to export a shapefile into PostGIS via a Python script from QGIS. I’m using QGIS 3.10, PostgreSQL 10 and PostGIS 2.5 on Windows 10. I tried go use qgis:importintopostgis: import processing ...
0
votes
2answers
60 views

Unexpected performance for OpenFileGDB driver compared with PostGIS

I am using GeoTools with OGR plugin to read .gdb data, and I found that the performance is rather horrible: Test code: public static void main(String[] args) throws MalformedURLException { ...
1
vote
1answer
38 views

How can I change the “NoData” value for a raster column in PostGIS?

I have a raster column with "NoData" value of 3.40282346638529e+038 in PostGIS. I want to change the NoData value to 100. I used the following code: SELECT ST_Reclass(ukc,1,'3....
0
votes
1answer
12 views

Compiling on CentOS fails due to ignored LIB flag

I need help with installing PostGIS. In general: I try to install it on a cluster (CentOS) and do not have admin rights. There is a sqlite3 installation (connected to the error), but this seems ...
1
vote
2answers
120 views

“ERROR: operator does not exist: geometry public.&& geometry” after changing PostGIS extension schema

I just moved PostGIS extension (I have version 2.5) from public schema to another (mob) schema by following the docs at this official PostGIS page. I did: ALTER DATABASE mob_odm SET search_path = ...
1
vote
1answer
98 views

Inserting GeoJSON in PostgreSQL using laravel controller

Actually.. I have GeoJSON that I send from javascript into controller. My GeoJSON: { "type": "FeatureCollection", "features": [{ "type": "...
2
votes
1answer
42 views

PostGIS slow ST_Intersection

I'm trying get get intersections between one straight line and some contour lines. Using the SQL below: DROP TABLE IF EXISTS v_line; SELECT st_geomfromtext('linestring(103.492217095662 30....
0
votes
0answers
22 views

Building PostGIS 3.0 and PostgreSQL 12 with confidence on Ubuntu 18.04?

I have failed in all previous attempts to build PostGIS on macOS, centos, or ubuntu in a manner that results in the creation of the address-standardizer and the reverse geocoder. I have tried all the '...
1
vote
1answer
47 views

Joining line segments in QGIS/PostGIS

I'm trying to join touching lines within a linestring dataset, so that I end up with a single feature for each set of touching features (eg the set outlined in green below). I've tried pretty much ...
2
votes
1answer
46 views

Union MVT features based on attribute / id in OpenLayers render

Following Paul Ramsey's article about ST_Subdivide I was able to split the Natural Earth boundaries dataset. Generating tiles which intersect the boundaries of Canada and Russia on the fly is now ...
1
vote
0answers
36 views

PostGIS rotating a line string to be vertical when viewed on screen

I'm building a Node.js application that will display Google Maps satellite images of golf course holes. I have 2 GPS points I'm pulling from a PostgreSQL database and I need to be able to take any ...
0
votes
1answer
73 views

PostGIS & Pagination

I have a pretty standard table with a geom column and a gist index on it. I would like to have a fast pagination on this table as it contains millions of rows. The query involves a ST_Intersects: ...
1
vote
0answers
40 views

Extending OSM database with new data

I want to add a new table inside my OSM database with extra data for every restaurant, and then when retrieving the places join the table by the "osm_id", is this possible and if yes, any ...
1
vote
0answers
46 views

PostGIS Raster layers not rendering in QGIS 3.14

I am having an issue with the rendering of PostGIS raster layers using QGIS 3.14 I have a PostGIS DB with multiple rasters and on QGIS 3.10 these are displaying as they should. When trying to open the ...
-6
votes
1answer
179 views

What are “degrees” in SRID 4326 and why can't they use meters? [closed]

I want to return all the houses for sale within 500 meters from my own house. I have this query: SELECT * FROM houses_for_sale WHERE postgis.ST_DWithin(coordinates, $1, 500); It returns houses ...
3
votes
1answer
77 views

Updating table from another table with function ST_CONTAINS in PostGIS

I tried to update an attribute 'solved' of a polygon table (table name: polygon) by using an attribute 'solved' of another point table (table name: point). What I want to do is that if ALL the points ...
2
votes
2answers
171 views

Merging two isolines into one in PostGIS

How can I sum two isolines A and B (closed linestrings with values) into one isoline C with summed values on common parts? For each line string there is a value and I would like to sum on common part. ...
0
votes
0answers
49 views

PostGIS, ST_Intersects & LIMIT performance question

I have a pretty standard table with one geometry column and a datetime column. Geom is indexed using gist and table is clustered + VACUUM ANALYZE. The Docker image is kartoza/postgis:12.0. I consider ...
2
votes
1answer
59 views

PostGIS validate the Z continuity

What is the solution to validate if a 3D linestring is always going down? All linestring nodes must have a lower value than the previous one, to check if Z values in a line are going down continuously ...
1
vote
0answers
39 views

QGIS Versioning plugin

I have created a database from QGIS Cloud free account. I have added a Post GIS layer into QGIS desktop. i have installed versioning plugin and added a group. After Selecting a Group when Hostorize ...
0
votes
0answers
17 views

Creating ensemble and aggregates from netCDF using PostGIS

I have downloaded climate forecast raster data in NetCDF 3.6 format for a series of models and time resolution of one day. The data is one file per model. Eventually, I would like to have an ensemble ...
1
vote
1answer
72 views

PostGIS query used to run very quickly on Postgres 9.6 / PostGIS 2.4, now extremely slow on Postgres 12.4 / PostGIS 3.0

We have two virtual servers on the same hardware (identical specs, 6 core Intel Xeon E5-2643), one is running Postgres 9.6 with PostGIS 2.4, and the other is running Postgres 12.4 with PostGIS 3.0. ...
0
votes
1answer
30 views

PostGIS ST_Area unexpectedly returns 0

Can someone help me understand why the following ST_Area query returns 0: SELECT ST_Area(ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[-180.0,0],[180,...
0
votes
0answers
10 views

Performing spatial queries with the SODA API in Python

I am trying to fetch data from data.cityofnewyork.us with the Python client sodapy. It generally works as it should, but I just can't figure out how to perform spatial queries. For example, I would ...
0
votes
1answer
38 views

using ogr2ogr to import a shapefile into a PostgreSQL/PostGIS database [duplicate]

I am trying to load a shapefile into a PostgreSQL/PostGIS databases with the following statement run within the OSGeo4W shell ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=...

1
3 4
5
6 7
158