Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
7,893
questions
2
votes
1answer
37 views
PostGIS: Creating Polygon from lots of other geometries and then returning list of ID's in the same query
I am fairly new to all of this but in simplistic terms I have the following problem:
I need to create lines from points, and from said points group all of those within n distance of one another. From ...
1
vote
0answers
45 views
How to export vector tiles from a PostGIS raster database?
I have a PostGIS raster database with 5 tables loaded from 5 geoTIFF images, tiled 100x100. I am trying to setup a map tile server to display this data on an openlayers map. I need to transform the ...
1
vote
0answers
37 views
geopandas to_postgis: ValueError: geom (geometry(MULTILINESTRING,4326)) not a string
I have a GeoPandas GeoDataFrame with a column named geom having MultiLineString values as such:
print(gdf.to_markdown()) # using package 'tabulate' to display a markdown table
| | uid | key | ...
1
vote
0answers
41 views
Is this a safe way to enforce non overlapping polygons or can it cause data corruption?
In this answer and comments it is explained that it it possible to implement a non overlapping polygon constraint using exclude and a custom operator. This is using PostGIS and not the built in ...
1
vote
0answers
22 views
Union table of raster tiles in PostGIS
I have a table of tiled rasters. They often overlap with each other. For instance:
I would like to eliminate the overlaps. Where a raster overlaps with another raster, it should take the larger value ...
1
vote
0answers
50 views
Filling in gaps between polygons using PostGIS functions
I have a similar question to Filling gaps between polygons using QGIS
The solution talks about using it in QGIS but I want to use PostGIS functions on a table on the geometry column.
The solution ...
0
votes
0answers
25 views
Appending the features to PostGIS with PyQGIS
I have two layers with same schema and same name but its contain different features.
I need to append those features with PostgreSQL db layer. All three Layers(db layer) schema and name is same .
def ...
2
votes
0answers
42 views
Using shortest path pgr_dijkstra
Versions
PostGIS Bundle 3 for PostgreSQL x64 12
pgAdmin 4.24
pgrouting 3.1.0
I have also enabled the postgis and pgrouting extensions on a public schema.
I am only just starting with pgrouting so I am ...
1
vote
2answers
70 views
PostGIS - Fast area computation on large overlapping (or not) polygon dataset
I'm co-developing an PostGIS based API that will work with complex and potentially large sets (>10k) of polygons, extracted from a large table following custom, user defined filters. I have ...
1
vote
1answer
38 views
Combining points into polygons with range of values using ST_ClusterDBSCAN and ST_ConvexHull
I tried to create polygons with similar depths based on values from point data.
I got some help to start with in this question Combining points into polygons with range of values using PostGIS
Since ...
0
votes
0answers
14 views
pgr_createTopology does not create vertices at every end of the lines of a street network
I am using pgRouting with a street network. I added lines to the network that represent straight lines from destination points I am interested to the nearest street using ST_ShortestLine(). Now I ran ...
1
vote
1answer
47 views
Issue with different values after reprojection using GDALWARP (epsg4269 -> 3857)
I need to reproject a raster file (.tif) from EPSG 4269 to EPSG 3857 (OpenLayers default) to be used in PostGIS and tiled and served to an OpenLayers front end. I used the following command to ...
2
votes
2answers
84 views
Create point geometry in PostGIS from a X Y Z M coordinate format
I would like to get a PostGIS point table from a PostGreSQL table containing OpenDataKit data where the coordinates are contained into in a column in a X Y Z M format (M being I think the value of the ...
0
votes
1answer
59 views
Change geometry type to Polygon and MultiPolygon in PostGIS
I try to insert geometry values into a column in PostGIS. However, the type of values is a mix of MultiPolygon and Polygon.
I tried to change the column type to Multipolygon with:
Alter table ...
1
vote
0answers
30 views
How do you access PostGIS geometry data from .NET
I have a PostgreSQL / PostGIS database with both geometry and non spatial columns
I created a new Console App .Net framework project and added these nuget packages
EntityFramework
pgsql
...
1
vote
0answers
40 views
SRID Transformation in PostGIS giving inaccurate transformation
I am trying to make a transformation in PostGIS from ESPG 4326 to ESPG 5247. The problem is that when the data is imported into ArcMap 10.7 it is clearly in the wrong place (by about 2.5km). I have ...
0
votes
1answer
42 views
Selecting lat | long | name with given “polygon(,,,,,,,,)” in PostGIS using Python
My table in postgis is this with name samplecol:
vessel_hash | name_city | station | speed | latitude | longitude | course | heading | timestamp | the_geom ...
1
vote
1answer
24 views
PostGIS unknown projection id in ST_Transform proj4 string
Using PostGIS I'm trying to transform a point coordinate from EPSG:31468 to EPSG:4326 using a proj4-string that I obtained from QGIS "Select Datum Transform" (is it one?):
SQL is of the ...
0
votes
0answers
14 views
Automatically adding vertices to network in pgRouting
I am trying to add extra vertices based on the intersections of my edges with a polygon to get access points to those polygons, from where I could run pg_drivingDistance.
Therefore, it would this way:
...
2
votes
1answer
67 views
Combining points into polygons with range of values using PostGIS
I have a PostgreSQL table with a few million rows of [id, height, point] where the points are located in a ~100x100 grid.
I want to extract polygons from this data by combining points into polygons ...
7
votes
1answer
99 views
Why are my icosahedron triangle subdivisions not equilateral (PostGIS)?
I am creating a geodesic polyhedron using PostGIS. I am dividing the base icosahedron points (see Appendix A), into the first level of Class I subdivisions.
The setup
For simplicity let's take just ...
0
votes
0answers
18 views
Fragmentation in multicoloum geometry data type
I have a table with place-identifier(big int) and geometry column combined together as b-tree unique index in PostgreSQL. Upon insertion of 20k records the dB table is getting fragmented around 45-50%....
0
votes
1answer
22 views
PostGIS: Can´t preview geometry when in different schema than public - why?
So I'm trying to wrap my head around PostGIS and how to get it working correctly in a schema other than 'public'.
I followed this tutorial, and transfered PostGIS to my own schema, postgis_test. I ...
1
vote
1answer
50 views
Primary Key field needs to be filled with UUID in QGIS before insert
I have a postgres database with a postGIS extension. Some of the fields in the database tables are primary keys that are automatically being generated either with a UUID genarator or based on another ...
2
votes
0answers
33 views
Querying over multiple bands at one point
I have a multiband raster in PostGIS. I can obtain the value for a specific band at a specific point with
SELECT ST_Value(rast, band, ST_SetSRID(ST_MakePoint(x, y), srid))
FROM table;
I have 12 bands ...
0
votes
0answers
23 views
GeoServer and OpenLayers 3
I have developed a web app using GeoServer, PostGIS and OpenLayers3 in localhost. This app is working in localhost and now I would like to publish this website in public to collect data. Does anyone ...
2
votes
1answer
82 views
ST_Intersects slow across two tables
I have a set of municipality maps, with an ID for each municipality, and a Multipolygon for the geometry. In PostGIS, I've put these data into tables. Table 1 has the municipalities in year 2019 (each ...
-1
votes
1answer
45 views
Merging line layers and then splitting the result at a point layer using Python script
I am very new to Python and I need to write a function which filters two line feature classes by type, then merges the results, then splits the merged-lines at a filtered-by-type point feature class. ...
0
votes
0answers
35 views
Getting province and city name of given point
OpenStreetMap has many shapefiles for downloading, and with shp2pgsql we can import it to PostGIS
shp2pgsql -s SRID SHAPEFILE.shp SCHEMA.TABLE | psql -h HOST -d DATABASE -U USER
How can I get ...
0
votes
0answers
21 views
Using St_Intersects to find roads crossed on a journey
Is it feasible to make a query on a spatial database to find out how many main roads need to be crossed on a journey between two point features? I understand there is a ST_Intersects function but can ...
1
vote
1answer
65 views
What is the reason to use GeoServer to provide access to vector data? [duplicate]
I am building a website that will present spatial data (Leaflet) and I plan to include functionalities like: add, move, delete features (i.e. by clicking on the map or on the feature) I would like to ...
0
votes
0answers
27 views
Creating solid polyhedral surface from extruded 2D polygon using PostGIS
How can I create a solid polyhedral surface from extruded 2D polygon? I have created a closed polyhedral surface but ST_MakeSolid does not work for me.
Is there something wrong with the code below?
...
1
vote
1answer
45 views
Merging Polygons using ST_Multi ST_Union group by column value
I'm trying to merge polygons together to form a larger 1 and I'm getting some odd results
In the picture the red is giving the correct outline but there are 11 identical polygons I was expecting ...
1
vote
1answer
17 views
ogr export gdb to PostGIS doesn't contain geometry for display in GIS [closed]
I'm currently trying to export a .gdb containing polygons to PostGIS which other users will need to display using any software like QGis or ArcGIS.
I'm using ogr fom osgeo to export the GDB to PostGIS ...
0
votes
0answers
30 views
Django ORM geo function ST_AsMVTGeom always returns type “bytea” not “geometry”
I want to use POSTGIS function ST_AsMVTGeom with django ORM. Therefore I create following geo function:
from django.contrib.gis.db.models.functions import Transform, GeoFunc
class AsMVTGeom(GeoFunc):
...
6
votes
1answer
177 views
How to remove overlaps and keep highest priority polygon
I have a multipolygon table with overlapping and non overlapping multipolygons. Where they overlap I need to keep the one with the highest (=1) priority and erase away from the lower priority ones to ...
1
vote
1answer
60 views
Count number of different points within all polygons?
I have 2 PostGIS tables in PostgreSQL: polytable and pointtable. The point table has 1000s of points and a class column with 6 different unique classes (integer values from 1 to 6). The polygon table ...
1
vote
0answers
22 views
TSP function with pgrouting
I'm trying the TSP function with pgrouting with just a few of the many nodes obtained with osm2pgrouting (I also have the edges, of course).
I'm not an expert but once I run the query, it works and ...
2
votes
1answer
74 views
Finding Station order number along way using PostGIS
In QGIS and PostGIS I have two layers:
way (id, name, geom (multistring))
station (id, name, geom (polygon))
How do I find the station order along each way?
I tried using code of Finding all ...
1
vote
0answers
37 views
How to create a tile from a TIFF stored in PostGIS using Mapnik in nodejs
I stored TIFF in the database using:
raster2pgsql -c -C -s 4326 -f geom_raster -F -I evapo_01_201.tif -t 200x200 public.rasters | psql -U postgres -d gaaf -h localhost -p 5432
I think it's alright ...
3
votes
1answer
95 views
QGis - From highway mileage to longitude latitude
I'm using QGis and would like to convert the progressive mileage of a highway into latitude/longitude coordinates, but I have no idea how to do it.
I have a Csv dataset where there are several "...
3
votes
1answer
41 views
Having problem with PROJ string for custom Coordinate System
I was looking for an exhaustive reference on instructions supported by PROJ string, but couldn't find anything yet. Only this page in PROJ library docs - I guess that's all there is.
I have quite a ...
1
vote
1answer
61 views
PostGIS where clause not working as expected with multipart geometry
I am running an intersect from a subset of one table on itself (this is part of a larger parameterized query but the issue in question arose out of this particular situation) using a CTE (zones in the ...
1
vote
1answer
45 views
What projection does ST_Area(geom, true) use?
ST_Area documentation states that "area is determined on a spheroid with units in square meters" when using geography as argument, or when setting the 2nd parameter as "true".
...
0
votes
0answers
26 views
How to find shortest route between ANY source for ALL targets?
I have a list of starting points and a list of target points for pgRouting. I want to calculate the shortest path for every pair of [source,target]. I am using a subselect to pass an array of node id'...
1
vote
3answers
70 views
How to calculate shortest path dynamically considering other shortest paths calculated in same run?
My topic is to minimize the cost of construction line digging. Starting from a source point I have several households that need to be connected to the that line. My plan was to calculate the shortest ...
1
vote
1answer
41 views
How to identify pgrouting nodes I am interested after splitting road network in for many to many routing?
So I have a list of source points and a list of destination points. I also have a street network and I have created connection lines using ST_ShortestLine() between source/destination points and the ...
0
votes
0answers
37 views
Simplify/generalize vector geometry with side bias
I have contour polygons as vector geometry with attached elevation attributes.
Is there a method to simplify vector polygons while preserving topology and elevation bias?
By elevation bias I mean that ...
1
vote
1answer
29 views
GeoServer vector tile preview fails to load SourceMap
Very new to GeoServer and web mapping in general. My end goal is to publish data from PostGIS through GeoServer and OpenLayers. I have PostgreSQL/PostGIS and GeoServer on localhost.
When I try to ...
1
vote
0answers
47 views
Using large rasters in PostGIS?
I successfully imported a 7 gb raster into my PostGIS database using the following script
raster2pgsql.exe -I -c -d -t 100x100 -s <SRID> <file_path> public.<table_name> | <...
