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

PyQGIS - Adding layer from PostGis database on QGIS interface

I have the same problem, bellow my code if some one can help me please. from qgis.core import * from qgis.core import QgsProject from PyQt5.QtCore import QFileInfo from qgis.core import QgsVectorLayer,...
2
votes
0answers
43 views

Geom and ST_AsText(geom) gives different intersection results for same geometries [duplicate]

I am working to get the count of points intersecting with a convex hull. When I use the query in 1 it gives a count different than count in query 2. Only difference is that in query 1 I am using ...
1
vote
1answer
32 views

Convert SDO_Contains in PostGIS?

I have a longitude and latitude values and need to convert Oracle SDO function to PostGIS. My Oracle query is below: SELECT geoid FROM tract WHERE SDO_Contains( 'GEOM',SDO_GEOMETRY( ...
0
votes
0answers
26 views

Can't access PostGIS data after Ubuntu update [duplicate]

Our PostGIS database is installed on Ubuntu. Recently I made an upgrade to Ubuntu 20.04. Now we can't access any PostGIS data. Example: postgres=# select * from default_line limit 10; ERROR: could ...
3
votes
2answers
116 views

Update points geometry while snapping them to nearest line

I have two tables in the PostGIS database - points and lines. And I want to update points geometry to snap them to line if they lay within a certain distance. By now I managed to create a new table ...
2
votes
2answers
81 views

Rasters to PostGIS

I have been able to load rasters into my PostGIS database using the raster2pgsql tool and running it via the OSGeo4W shell. Files load where I want them to but appear in the database as one line files ...
0
votes
1answer
78 views

Publishing web map on website using PostGIS, GeoServer and OpenLayers

I have been trying to create a web map using PostgreSQL/PostGIS - GeoServer - OpenLayers. However, the resulting map is on my localhost and the data published in GeoServer can be accessed solely ...
0
votes
1answer
32 views

Linestring table (of MultiLinestrings) ST_Intersection

I'm moving from GeoPandas to PostGIS and GeoAlchemy. I've a table full of multilinestring geometries (timezones from NaturalEarth.com) and I've a linestring which represents what will be the route of ...
1
vote
0answers
35 views

Error while creating PostGIS entity with Hibernate and GeometrySerializer : No qualifying bean of type 'GeometryParser<?>'

I tried to create a simple application with a geometry persistent layer (Spring boot with hibernate-spatial) Here is my Entity class : // Annotations from lombok project @NoArgsConstructor @...
0
votes
0answers
25 views

Making Hibernate Spatial DWITHIN compatible with both H2GIS and PostGIS

I have following: @Query("SELECT f FROM Feature f WHERE DWITHIN(f.geometry, :centre, :range, true) = TRUE") DWITHIN is hibernate spatial function explained here. boolean dwithin(Geometry, ...
0
votes
1answer
34 views

Geometry errors when importing shapefile with shp2pgsql PostGIS

I'm trying to import the Census State Boundaries from the census data site although I get errors in the geom data every time I import in the same rows. I've tried shp2pgsql and ogr2ogr shp2pgsql -I -d ...
0
votes
0answers
13 views

Any way to get postgis_raster extension working on Google Cloud right now?

I needed to use the St_AsRaster function for the first time in a long while, and I was surprised to see that it was no longer available. Fair enough, it now belongs to postgis_raster. But that ...
1
vote
1answer
167 views

PostgreSQL-12.3 / PostGIS-3.0 - could not access file “$libdir/postgis-2.4”: No such file or directory

I am aware there are a few similar questions to this one, such as here and here. My problem is however a bit different. I've recently upgraded my Ubuntu to 20.04 and there only PostgreSQL 12.3 and ...
1
vote
0answers
32 views

How to retrieve raster's metadata from PostGIS, using Python?

I am working on a PostGIS database, and I would like to store GeoTIFF raster images in some of my tables. Currently, I use GDAL to convert / read my images, and psycopg2 to connect to a SQL server. ...
1
vote
1answer
38 views

Clip raster with postgis outputting result in clipping geometry extent

I want postgis st_clip to output clipped result in extent of initial clipping geometry. Currently, it's crop option can be set to output either in initial raster extent or in extent of intersection ...
2
votes
0answers
37 views

pgRouting: pgr_KSP() does not return shortest path when k > 1

I am trying to get an arbitrary number of paths between two points in a network. I am using pgr_KSP — Returns the “K” shortest paths. My aim is to get the K cheapest routes between two points. ...
0
votes
1answer
33 views

Search for all points within a bounding box

I've already checked all previous similar question. I'm asking again just because I need to be more specific by describing the database structure I am working with. CREATE TABLE stores (name varchar, ...
0
votes
0answers
43 views

GeoServer returns “schema does not exists” when creating SQLView Layer using REST API

I'm using GeoServer 2.16.2 (Kartoza/geoserver + postgis docker image) and I'm trying to create a SQLView layer using the REST interface. First I manually created the SQLView layer and copied the layer ...
1
vote
0answers
21 views

How to extract lat, long coordinates from Geography type column in dataframe using SQLAlchemy?

This is the SQL command that works perfectly. SELECT lonlat, ST_Y(lonlat::geometry) AS latitude, ST_X(lonlat::geometry) AS longitude This is the output of the SQL command. lonlat ...
2
votes
1answer
28 views

How to refer to another table in a CASE WHEN Statement in PostGIS?

I'd like to set column3 in table1 to a specific value by checking if a certain threshold (Queried by intersection) is exceeded. My first table holds geometry information with additional columns,the ...
0
votes
1answer
31 views

How to create geometry from set or array of lat/lon in table using PostGIS?

My question is similar to Creating geometry from lat/lon in table using PostGIS? , but I need to convert set or array of LON & LAT in PostgreSQL into geometry value. For example, I can convert ...
2
votes
1answer
46 views

PostGIS ST_Union with IF statement

I have a dataset containing buildings where each building has a certain value/score. What I want to do is make clusters of these buildings depending on their values/scores. What I did so far is; I ...
0
votes
2answers
53 views

Calculate median width of road polygons

I'd like to calculate the median width of a polygon in PostGIS. The problem is almost identical to the one described here, which has an excellent answer. My question is how to calculate the median, ...
3
votes
1answer
99 views

Fastest point-in-polygon solution with billions of points over thousands of polygons

Problem definition is quite simple: I need to achieve point-in-polygon detection for: billions of lat/lon points over thousands of polygons I need to be able, for every point, to determine if it is ...
3
votes
1answer
35 views

Selecting distinct coordinate pair in PostGIS

I have a PostGIS table containing four columns: x, y, k_x, k_y Each row represents a relationship between two points. Many of the relationships are duplicates albeit in the other direction, like so: ...
0
votes
0answers
44 views

ST_Buffer() ST_SummaryStats conflict in PostGIS

I created a segmented line and buffered each segment with a 4m buffer in PostGIS. After running ST_SummaryStats() on the geom element of my segmented line I get a lot of NULL values in my attribute ...
0
votes
0answers
56 views

Optimising Cross Join PostGIS Query

I have tried performing the query shown below via ogr2ogr and the gpd.GeoDataFrame.from_postgis() method in geopandas. from osgeo import ogr import psycopg2, os import geopandas as gpd import pandas ...
2
votes
1answer
59 views

create lines from points table

I have a point table I have sorted by mmsi (it is the identifier) and by datetime columns. mmsi numeric, x1 double precision, y1 double precision, datetime timestamp without time zone, geom ...
-2
votes
1answer
142 views

PostGIS Clustering with MAX and MIN SUM Limit of Attribute

I have point data with number of peoples living in a house. wkt111,members SRID=4326;POINT(8.40771254932301 52.8935196103868),158 SRID=4326;POINT(8.35609033091261 52.8948388844689),122 SRID=4326;POINT(...
0
votes
2answers
29 views

PostGIS Web service call function

I have spatial function in my Postgres/PostGIS DB that when sent a set of coordinates (Long Lat) returns single value. Whilst I'm not a web developer, i'd like to serve the function up as a web ...
1
vote
1answer
27 views

Mixed SRID Geometries

I'm developing a PostGIS function to return a value when longitude and latitude are passed as variables to the function. CREATE FUNCTION flood_location(long float, lat float) RETURNS varchar(40) AS ...
3
votes
2answers
31 views

How do I retrieve a single image from an ImageMosaic in GeoServer?

If I have an ImageMosaic in GeoServer containing several raster images that overlap, is there a way to retrieve/display one of the original images? Could I use CQL or WCS to get them by fileName or ...
1
vote
0answers
22 views

Read R-Tree from PostGIS enabled database

There is a method available to get the largest rectangle from the R-Tree (see this question). Are there methods available that allow one to read the entire tree?
1
vote
0answers
25 views

Linking new feature violates foreign check constraint in PostgreSQL database when working in QGIS3

I have set up a spatial database in PostgreSQL in which I have some data/tables that need to be linked by M to N relations. For those occasions I have two tables that contains geometry and a pivot ...
1
vote
0answers
42 views

Approaches to delete Polygon overlap in PostGIS?

Is there function to delete the areas of overlap between polygon features to achieve visualized results? Id like to check for each geometry (row) if a geometry is intersected, or in other words if a ...
3
votes
1answer
178 views

PostGIS Zonal statistic (St_SummaryStats()) NULL problem

I try to assign each Polygon in my data set the maximum value that can be found within their areas. I have a raster and a collection of Polygons with each have a unique id (gid). I uploaded the ...
2
votes
1answer
102 views

Updating PostgreSQL database with PHP language

I want to update the text information contained in my database for a specific layer. This layer is called via a WFS stream. At first I displayed my layer with an Overlay with OpenLayers, and with a ...
0
votes
0answers
36 views

GeoServer WMS has missing features

I have created a WMS service rendering contour lines. The root data is in PostgreSQL. When displayed in OpenLayers many contour lines are missing. After panning or zooming, the missing features may ...
1
vote
1answer
50 views

PostGIS - Optimize Point Outside polygon query for 4 million points

I am trying to do a 'point outside polygon' query for GBR with ~ 4 million points. Most of these points are inside the boundary but a handful are falling way outside the boundary. The country shape ...
2
votes
0answers
24 views

Optimization of multi-band raster clip by polygon

I have tiled (50x50), 365 band, rasters in my postgis database. Each band represents a day in a year. I want to return the raster pixels which intersect with an input geometry, including all bands. I'...
3
votes
1answer
62 views

How to run a moving window function in a conditional statement in PostGIS for buffer artifact removing?

I try to remove the artifacts which occur when running the ST_Buffer function in PostGIS PostGIS 2.5.1 (See picture). For this case I wrote a function that should loop trough every segment (row) and ...
0
votes
0answers
17 views

Losing connection to the server when using PG Routing function pgr_alphaShapes

I am running PG Routing v. 3.0 on Postgresql 12.2 and the pgr_alphaShapes function is giving me a lot of trouble. Following the documentation here, I passed a geometry and a spoon radius. SELECT * ...
0
votes
1answer
38 views

Orientation of linestring with North arrow pointing up

With PostGIS I am trying to calculate the azimuth of a linestring in order to use it then in a print composer in QGIS, using an atlas. But since the azimuth is calculated by comparing startpoint and ...
0
votes
0answers
32 views

FOR LOOP fails in PostGIS [duplicate]

I try to develop some code that loops through a set of geometries (ordered by id) and deletes from the next neighboring geometry based on the size, but my syntax seems at the loop seems to be ...
0
votes
2answers
38 views

Geometry in PostGIS Table wrong SRID and type [closed]

I have loaded some points into a table as Multipoint, they have come through with an SRID of 0. I need to change these to have a SRID of 7844 and also change them to be of type Point. There are other ...
2
votes
0answers
43 views

Generating inverse polygons inside polygon

I'm designing a PostGIS database with country polygon and many layer polygons of water areas, inside these country polygon. I need to generate inverse polygons to water polygons, that doesn't contain ...
2
votes
1answer
42 views

Precise st_area() of small polygon need geography

My map is using UTM (my SRID), so it is in meters. The diff shows that it is not the same. What is the "most correct" value? (less floating point truncations and geometry-transformation errors) with ...
2
votes
0answers
33 views

PostGIS ST_Distance_Sphere speed-up

I am trying to build a geocoding app on OSM data, that in regard to arbitrary location will return points within specified radius, as well as distances to the points. I have the following table: id ...
3
votes
1answer
65 views

Measuring the similarity of two polygons in PostGIS

I want to measure the similarity of two polygons. I want to use the average distance of the boundaries as a measure of the shape similarity. How can I do this in PostGIS?
2
votes
1answer
44 views

Summarise duration of points within polygon

I have table with points that are gps coordinates for multiple vehicles. I have another table of multiple polygons that the vehicles travel through. I’ve managed to write a python script that ...