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
16 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
11 views

Linking new feature violates foreign check constraint in postgres 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
29 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 ...
1
vote
0answers
31 views
+50

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 called "mapping" and a collection of Polygons called "blocks" with each have a ...
1
vote
1answer
96 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
33 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
37 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
19 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. ...
3
votes
1answer
46 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
10 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
32 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
29 views

FOR LOOP fails in PostGIS

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
35 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 ...
-1
votes
0answers
19 views

Geopoints: From single coordinate to bounds on map [closed]

I try to figure out how to come from a single given coordinate (lat/lon) to the nearest bounds which enclose this coordinate on a map e.g. streets or sea. Here two examples to give you a better ...
2
votes
0answers
39 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
39 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
28 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
50 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
2answers
34 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 ...
-1
votes
0answers
17 views

Leaflet-GeoServer-PostGIS How do I add a filter to Leaflet [closed]

I create a web map using Leaflet, connected to GeoServer and PostGIS database. To access the database I need to give an option to the user in Leaflet web map, as a filter to chose between some codes. ...
0
votes
0answers
39 views

Installing PostGIS

I installed PostgreSQL 12 on Ubuntu server 18.07. After the installation, I need to install PostGIS 3. i run this command "sudo apt install postgresql-12-postgis-3". The following packages have unmet ...
0
votes
1answer
17 views

Check the minimum pixel-value in a Raster using PostGIS

I'd like to check some basic statistic of a raster in my database. How can I determine the smallest raster value found in all raster tiles (enroaching all srid's)? My code looks like: SELECT min(...
0
votes
0answers
26 views

raster2pgsql with tiling results in wrong pixel dimensions when tiles are st_unioned back together

Why is tiling messing with the pixel dimensions? I used raster2pgsql to create two tables test_hourly_1 and test_hourly_2 on the same source geotif. The first one doesn't use tiling and the second ...
0
votes
1answer
24 views

Populate field with drive time values during isochrone creation in Carto

I'm creating isolines (isochrones) in Carto to show 15, 30, 45, and 60 minute drive times from a set of points in a shapefile. Here is the SQL I've written to create those isochrones in an empty table....
3
votes
3answers
129 views

Making Polygon that covers the Northern hemisphere in PostGIS?

In PostGIS, I would expect ST_Geography polygons to use a clockwise winding rule, i.e. everything to the right of the edges of the polygon is inside of the polygon. Therefore, I expected a polygon ...
0
votes
1answer
28 views

Add view generated with psycopg2 through sshtunnel as layer

I'm writing an internal QGIS plugin to query a remote database. I'm not administrating the server, but administrator has set up a virtual machine on a windows file server, with PostGIS set up. I ...
1
vote
1answer
15 views

Replacement for pgr_pointToEdgeNode for pgr 3.0

I am working through the book 'pgRouting: A Practical Guide' and have come across a function that has been deprecated. The function is pgr_pointToEdgeNode. Is there a similar function I can use to ...
1
vote
1answer
41 views

Add an ESRI projected coordinate system to PostGIS

I was trying to add to PostGIS a new projected coordinate system, the ESRI World_Cylindrical_Equal_Area, 54034. I have searched for it in epsg.io but it is not available there. I would have liked to ...
0
votes
0answers
13 views

PostGIS 3 table join & classification on mapServer

I have been trying to create a mapServer map by joining 3 table then create classes on those joins (2000 - red, 2010 - green, 2020 - blue) I am not getting an error but noting is display $query = "...
1
vote
1answer
33 views

How to create line substrings with fixed lengths from a line ignoring inflection vertices in PostGIS?

I want to split a line in segments of a fixed length (e.g. 10m) like indicated in the picture. Using the approach of st_segmentize how-to-buffer-a-line-with-segments-in-postgis-using-unique-...
2
votes
1answer
35 views

Insert/Update/Delete table from another table changes in PostGIS

I'm trying to build a function that Insert/Update/Delete rows of a table based from another table changes. Unfortunately I could not continue, some problems occured: DELETE isn't work correctly: Any ...
1
vote
0answers
33 views

How to buffer a line with segments in PostGIS using unique segmentsize?

I want to buffer a line in PostGIS. For this purpose I created a line, split it into fixed segments (8m) and run the buffer function. --CREATE TABLE line(name char(10), geom geometry); --INSERT INTO ...
0
votes
1answer
12 views

PostGIS & QGIS 3.4 : convert mixed Linestring/Multilinestring geometry to multilinestring

I have a PostGIS table in which the geometries are mixed Linestring and Multilinestring. But QGIS doesn't like it very much (it splits the table by the different geometries in the "add PostGIS table" ...
0
votes
1answer
19 views

Cut linestring segment at centroid in PostGIS

In a table of linestring three segments are connected. first--second--third could Postgis generate the centroid of second segment and merge first and third at center? Is it possible? first--...
-1
votes
1answer
25 views

MariaDB vs Postgres (POSTGIS) perfomance difference?

Has anyone used mariaDB for storing spatial data? I am using postgis now. My queries are mostly "finding peope within certain radius". Happn app an app which shows you "the people you crossed path ...
1
vote
0answers
26 views

Best practice to upload/analysis raster vector data in PostGIS

I am trying to build a web-GIS platform where user can upload their data (raster & vector) and do some kind of analysis, like calculate zonal statistics. I read lots of articles and blogs about ...
0
votes
0answers
29 views

Best way to serve vector tiles from data that changes frequently?

I'm working on a project where I process national radar data and generate a GeoJSON with the data. The data is generated every 5 minutes and I delete the oldest data before I add the latest data. I'm ...
0
votes
0answers
65 views

Strange PostGIS errors after having altered its extension by specifying the postgis schema

Context I'm trying to refine a PostgreSQL (v.10.12) trigger in order to actually execute some computations on point features only if the input points do intersect a convex hull stored in an other ...
1
vote
1answer
18 views

How to add more parameters to the sql file created by raster2pgsql

I'm using raster2pgsql to upload rasters to a postgres database I execute the following command in cmd to create a sql that will add the raster "C:/Program Files/PostgreSQL/9.5/bin/raster2pgsql" -s ...
1
vote
0answers
21 views

Raster grid on PostgreSQL 12.2/PostGIS 3.0.1 not able to add to QGIS 3.12

I'm trying to do the example on PostGIS Cookbook by uploading the Temperature Grid exactly as shown in the book (https://subscription.packtpub.com/book/big_data_and_business_intelligence/9781849518666/...
0
votes
2answers
28 views

PostgreSQL database designer that supports PostGIS geometry data types

Does anyone know if there are PostgreSQL database modeling software or apps that support PostGIS geometry data types? I know pgModeler is supposed to but I have been unable to get this installed.
0
votes
1answer
24 views

'ST_SEGMENTIZE' function in PostGIS gives single output

I try to split a line into 2m segments but somehow the function ST_SEGMENTIZE returns the whole line assigning just one id. An example: CREATE TABLE line(name char(10), geom geometry); INSERT INTO ...
1
vote
1answer
38 views

ST_ConvexHull excludes some points

I'm trying to create convex hull for about 25000 points stored in the table. ST_ConvexHull run successfully, but resulting geometry does not covers some points. What could be a reason? And it is not ...
1
vote
2answers
55 views

Vector layer from MapServer WFS not displaying in OpenLayers 6

I want to display a WFS layer in GeoJSON format on my map via OpenLayers. All my layers in WMS appear, only the one in WFS does not appear. I did create a "WEB" block in the mapfile. And my layers are ...
2
votes
1answer
32 views

Performance optimization of ST_Touches

The query I am running is taking a considerable amount of time. I was wondering whether I am missing an opportunity to drastically improve performance. My goal is to find out how many neighboring ...
1
vote
1answer
25 views

OpenLayers: Incorrect rendering of WKT

I'm using the draw function of OpenLayers for draw a polygon. I need the wkt from the drawn geometry because I need to send it to PostGIS. <textarea name="name" rows="20" cols="80" ...
4
votes
1answer
76 views

Count number of points outside of Buffer?

I'm trying to count the total points outside of a buffer zone using PostgreSQL. The intention is to find all of the schools that do not fall within a 2 mile radius to libraries. I've either gotten ...
0
votes
1answer
37 views

imposm3 on PostGIS to mapnik conversions

I am working with osm2psql and imposm3 to load a postgres db with OSM data. The performance of imposm3 is seemingly far in advance of osm2psql (to a factor of 4 i am finding). However I cannot find ...
0
votes
1answer
29 views

What is the geometry type if I am having mixed geometry?

I have a mixed geometry data some belong to MULTIPOLYGON and others belong to MUTIPOLYGONZM and I want to insert the data into one
0
votes
1answer
38 views

How to Use ST_MakeLine query in leaflet to connect to Carto

I want to run in leaflet a query that works in carto Builder to connect points in a line: SELECT ST_MakeLine(the_geom_webmercator) as the_geom_webmercator, min(cartodb_id) as cartodb_id FROM my_table ...

1
2 3 4 5
150