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

Merge/union multiple row rasters into one

I stored Sentinel-2 images as tiled in the PostgreSQL DB. Now I am trying to clip it with geometry and it works fine, also converting raster to geoms (ST_PixelAsPolygons)is also working fine. ...
1 vote
0 answers
33 views

Slow loading of rasters from PostGIS. Not use overviews PostGIS

Very slow loading of rasters from PostGIS. The analysis showed that there is no coverage in the table, which means that overviews is not used. The raster is loaded into the database by the ...
0 votes
0 answers
20 views

ogr_fdw and Esri REST API date columns

I am trying to use ogr_fdw in my database to connect to Esri REST API services. I am coming up against a problem with fields that are of the type esriFieldTypeDate, with a length of 8. When using &...
  • 4,174
2 votes
1 answer
42 views

How does ST_ApproximateMedialAxis(geometry) calculate medial axis internally?

I am curious about the internal working of ST_ApproximateMedialAxis(geometry) when a complex polygon is passed to this function. How does it calculate the medial axis internally?
1 vote
1 answer
37 views

Are there any limitations on the functionality of PostGIS related to the version of PROJ?

PROJ >= 7 optionally requires curl and libtiff. Are there any limitations on functionality of PostGIS if PROJ is not installed with them which are optional? I would like to use all functionality of ...
  • 11
1 vote
0 answers
16 views

QGIS Modeller- PostgreSQL execute and load- prevent layers being added to map?

I have a model which loads a bunch of PostGIS layers, does some processing, then adds the styled output to the map. A side effect of the 'PostgreSQL execute and load' algorithm is that it loads those ...
  • 340
5 votes
0 answers
62 views

Long QGIS project loading time due to QgsPostgresProvider SQL calls

QGIS 3.22.8 LTR. Setup I have a QGIS project file which contains around 100 PostGIS layers, hosted in Azure PostgreSQL (similar to AWS RDS). All layers were added via 'Add PostGIS Layers...' tool (not ...
  • 1,597
1 vote
1 answer
42 views

Converting 2D Polygons to 3D in QGIS/PostGIS

I have a buildings layer in QGIS that is stored in Postgis. The layer is MultiPolygon type although I am pretty sure there is only 1 geometry (Polygon) per element. I was able to create a height ...
  • 3,598
0 votes
0 answers
24 views

Upgrading Fedora Server to 37, Postgres' upgrade didn't work: ERROR: could not access file "$libdir/postgis-3"

I went through the related questions / answers here and found none applicable, though a few similar. After everything else was shown to be working fine (YAY!), Postgres refused to start (don't be ...
  • 101
1 vote
1 answer
37 views

How to illustrate geodesic line reflecting bearing between two points + third projected point

I'm using Spatialite + ST_Project to extrapolate a line with an implied bearing between two points to some distance beyond those two points. Here's an example sql snippet: SELECT AsGeoJSON(ST_Project( ...
1 vote
2 answers
36 views

PostGIS TIN to Raster

I am trying to convert a TIN to raster, in PostGIS, with a 1m cell size but sampling the tin for elevations is very slow. My test data is about 2km x 2km and relatively flat, think farm field. Below ...
  • 932
2 votes
2 answers
93 views

Computing polygon-arc table and left-right polygon table in PostGIS

A classic data structure for describing the topology of a polygon layer is the polygon-arc table and left-right polygon table as described in the ArcGIS documentation and the following illustration ...
  • 10.8k
2 votes
0 answers
51 views

Convert datum of elevation data in PostGIS

Is there any specific feature to convert raster values (representing elevation) between basis systems, or do I just have to make do with manually adding the mean offset having looked it up from ...
  • 191
4 votes
1 answer
138 views

Why is this polygon not planar for PostGIS

Why is the following Polygon Z not planar for PostGIS 3.4.0dev (3.3.0rc2-335-gae95dd219)? SELECT ST_IsPlanar( ST_GeomFromText( 'POLYGON Z (( 3021241.9043000005 2037823.3759000003 262....
  • 8,771
1 vote
1 answer
44 views

QGIS 3.28 : Relational value form (spatial intersection)

With QGIS 3.28 I have a point layer (boreholes) . And I have a polygonal layer (departments/districts). My goal would be to via QGIS (right click boreholes point layer -> attributes form) to ...
  • 811
0 votes
0 answers
45 views

Error in PostGIS executing custom EPSG conversion (ST_Transform)

I have customized the EPSG 38599. Its proj4text is as follows: +proj=pipeline +step +proj=tmerc +lat_0=0 +lon_0=114 +k=1 +x_0=500000 +y_0=0 +a=6378137 +b=6356752.314 +units=m +no_defs +step +proj=...
3 votes
1 answer
71 views

PostGIS SQL: Internal buffer of unioned polygons

I have a polygon layer containing titles which are grouped by a property ID (i.e. there may be one or more title polygon per property) I need to generate an internal buffer of those unioned titles/of ...
  • 340
2 votes
2 answers
99 views

ST_Tesselate on PolyhedralSurface is invalid : Polygon 0 is invalid: points don't lie in the same plane (and Is_Planar() only applies to polygons)

Using PostgreSQL 15/PostGIS 3.4.0, I am having troubles when tessellating some polyhedral surfaces. Indeed, I have a table where 99% of the surfaces are giving correct tessellation results, but few of ...
  • 8,771
4 votes
1 answer
76 views

Merging polygons based on percent overlap in PostGIS

Here is a simple representation of my table of polygons: I want to merge the small polygons to the big ones (if they intersect) and I can't do a simple union because of cases like polygon 1 would ...
  • 526
0 votes
1 answer
45 views

Merge several TIN in PostGIS

I have plenty of very tiny adjacent TIN chunks in a PostgreSQL 15 / PostGIS 3.4 database. I would like to merge them all into a single one. I've tried both ST_Collect and ST_3DUnion but they both ...
  • 8,771
3 votes
1 answer
63 views

How to split a linestring by its Z dimension

I have a tracks table with a geom column of type geometry(LineStringZM,4326). I now need to find out how much time a user has spent at a given elevation range. For this I'm gonna create a materialized ...
1 vote
0 answers
31 views

Convert a LineString Z into a PolyhedralSurface in PostGIS

Follow up on this, if possible, how can I convert a geometry from a closed MULTILINESTRING Z into a POLYHEDRALSURFACE Z in PostGIS 3.3+? This question is the perfect opposite. Sample data: SELECT (...
  • 8,771
0 votes
1 answer
37 views

PostGIS intersect two polygons from geom tables PostgreSQL

I want to intersect two polygons; they are in two tables stored in PostgreSQL. The names are "poligono" and "categorias_eot_paramo", respectively. I am using the next simple syntax ...
1 vote
0 answers
36 views

Bounding box not working well in trigger PostgreSQL - PostGIS

My trigger function consists of collecting the vertices of a polyline table "depart_bt", searching for the point entities of the table “support_bt” which coincide with the vertices of the ...
  • 843
0 votes
0 answers
27 views

Polygonize a vertical plane defined by a MultiLineString Z in PostGIS

I have to polygonize a vertical plane defined by a MultiLineString Z: SELECT ST_AsText((ST_Dump(ST_Polygonize(ST_GeomFromText( 'MULTILINESTRING Z (( 0 0 3, 1 1 3, 2 2 0, ...
  • 8,771
2 votes
1 answer
40 views

Setting the id column as a PostgreSQL identity field when inserting data into PostGIS with ogr2ogr

I want to tell my ogr2ogr command which inserts data from a ESRI Shapefile into a PostgreSQL/PostGIS database to create the id field as a PostgreSQL IDENTITY column (not as a SERIAL). For the moment, ...
  • 8,771
1 vote
0 answers
41 views

Color a tile while creating in PostGIS

I am trying to create a tile using PostGIS. Although I am able to create a tile but facing difficulty when coloring it. I want to color the tile means I want to color the geometry with different ...
  • 33
1 vote
1 answer
63 views

Performance of PostGIS Query

Here's my query SELECT "building"."id", "building"."details", "building"."status", ST_AsGeoJSON("building"."center")::...
  • 111
0 votes
0 answers
28 views

Unlock a PostgreSQL/PostGIS table

I created a PostGIS table like this: query = "CREATE TABLE public.\"" + newlayer + "\" AS select * FROM \"" + orglayer + "\"; " + "DELETE ...
  • 163
0 votes
1 answer
39 views

Disable JDBC driver SSL connection attempt with GeoNetwork/PostgreSQL-PostGIS DB

Following to my previous post : GeoNetwork 4.2.1 (Debian) install : error message? The problem seems to come from the JDBC driver wants to connect over SSL. Do you know why JDBC driver wants by ...
  • 811
1 vote
0 answers
32 views

PostGIS function to get the corresponding intersection matrix pattern for any of the 8 spatial relationships between two geometries

I have a quick and very simple question for which I have not (yet) been able to find an answer: is there a PostGIS function to get the intersection matrix pattern corresponding to any of the 8 ...
  • 8,771
0 votes
0 answers
16 views

raster2pgsql -C generates incorrect extent constraint

I am trying to load multiple rasters into the same table with the raster2pgsql. I verified via rasterio's dataset.profile that all of my rasters have the same bounds, centroid, and dimensions. Using ...
4 votes
0 answers
77 views

PostGIS Topology result still has gaps and overlap

I have been trying to build a Topology in Postgis with 4000+ polygons. They are not cleaned, with a lot of gaps and overlaps. After using ST_MakeValid, creating the topology and adding topogeom column,...
2 votes
1 answer
68 views

Why can't I write this SimpleFeature (Line)?

I'm trying to create simple lines in SRS UTM (32633). The procedure worked fine in GeoTools 20.5 and PostgreSQL 11. Now I've upgraded to PostgreSQL14 and Geotools 26.5 In the loop I can write the ...
  • 163
1 vote
1 answer
61 views

Sorting rows based on calculated values on PostgreSQL's ST_DWithin

I have a question on how to improve sorting performance on PostgreSQL on geospatial data. I want to sort restaurants based on their distance and price, but it's noticeably slower than my study on ...
0 votes
3 answers
97 views

GeoServer WMS is slow when using OpenLayers and OSM

I am new to GIS, and attempting to create a map with PostgreSQL/PostGIS,GeoServer, OpenLayers, and OSM data. I am only serving the NA continent, but it is taking approximately 20 seconds to render the ...
1 vote
1 answer
29 views

Name arcs and nodes of a network of roads using only roads names

I have a network of roads composed of arcs (segment of road connecting two intersections) and nodes (intersection of two or more roads). Each road, which is composed of one or more arcs has a name (e....
0 votes
0 answers
30 views

ST_PixelAsPolygons returning incorrect results

I stored sentinel 2 images as a tiled raster in my DB. The tile size is 244x244. So I want to extract data from those rasters. Currently I am trying to export the data by giving a point and number of ...
3 votes
1 answer
105 views

Populating geom using lat/long or x/y

I'm having trouble populating a geometry (geom) column by using already existing columns (the data has an x, y, as well as lat, long columns) the 4 columns are in varchar. I have created the geometry ...
  • 31
1 vote
1 answer
50 views

Import GeoDataFrame into PostGIS using osm2pgsql

I use python and GeoPandas to preprocess data in a GeoDataFrame. Now I want to import these data into PostGIS. I know that I could do it with python directly but this is too slow. My application needs ...
3 votes
1 answer
206 views

PostgreSQL not taking full advantage of all computing resources

Basically I have one table buildings_by_cell_300m, About 2490000 rows. I want to statistic all the geom information within 50 meters of each geom. I am doing: CREATE TABLE gbmi.bn_by_cell_300m AS ( ...
  • 45
1 vote
0 answers
46 views

PostGIS splitting Linestring

I have a Street network stored in a Postgis database. All streets are represented by Linestrings. For a further Intersection with a DEM height raster, I would like to split the linestrings in smaller ...
0 votes
1 answer
20 views

GeoServer layers: Default value for extra values showing up even when other value is provided

I've got a PostGIS table that I'm publishing using GeoServer (2.21.1). I have multiple overlapping vector layers for different classes. I've also got an aggregate class included that is the average of ...
  • 1,336
0 votes
1 answer
38 views

Rasterio to Postgis without save file in disk

Does anyone know how to upload a rasterio object to PostGIS using Python? I have checked: https://rasterio.readthedocs.io Sadly, there is not too much info about PostGIS. I know it can use GDAL ...
  • 61
2 votes
1 answer
77 views

ST_Split only on blade

If the geometry input (a line string in my case) crosses over itself, ST_Split is splitting on the crossover as well as the blade. Is that the expected case / is it possible to only split on the blade?...
  • 21
1 vote
0 answers
24 views

GeoJSON or VectorTiles for dynamic large dataset - when to use which one? [closed]

Assuming that the goal is to develop a web application with a static basemap (can be raster tiles) and multiple layers, which: are stored in a PostGIS database have a big amount of features include ...
  • 39
0 votes
1 answer
53 views

Calculate direction in PostGIS & Django (GeoDjango)

Is it possible to calculate direction to detect if users are moving in "similar" direction having a few of their last coordinates (Points) using PostGIS + Django (https://docs.djangoproject....
2 votes
0 answers
27 views

Merging overlapping features into one file with attributes (PostGIS)

I am working with os topography files which are split into points, lines and polygons. At the moment, I am trying to merge the separate files into a single topography file. Using this source (Using ...
1 vote
0 answers
20 views

Passing table and column name to function in PostgreSQL [closed]

I'm trying to write a function which performs zonal statistics in PostgreSQL/PostGIS. I have a table of vector circles and want the mean value of a raster within each of those circles. I have code ...
  • 305
1 vote
0 answers
45 views

How to limit memory usage for pdal to import LiDAR data

While trying to import LiDAR pointcloud data in .las format (my file is 38GB) to PostgreSQL using the pointcloud extension, I keep getting Killed after several minutes of the following command running ...