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
0
votes
0answers
18 views

Adding polygon in PostGIS db not commiting only when using “add feature polygon” and not “add rectangle from 3 points”

I have a strange behaviour(bug) in QGIS 3.10 (tried with other versions too) under Win 10. I m using a PostGIS layer, with a PostgreSQL db which has a geometry column declared as "Polygon". ...
-1
votes
0answers
29 views

PostGIS When, Then, ELse [closed]

I have an empty column where I want to copy data from several other columns. When using the script below, it is doubling the number of rows. And when I try to use ELSE, I get an error message saying ...
1
vote
1answer
42 views

Transform coordinates [duplicate]

I have two shapes, one called bike_stations and another one called quite_streets, both contains geographic information about elements in Madrid, Spain and each one come from two different sources. The ...
1
vote
1answer
51 views

Counting Polygon Centroids in Polygons

I have two tables in PostgreSQL 13: hexbin table with geom field (red) sites table with geom field (blue) I want to return a table with the hexbin geometries and a total count of where the sites ...
0
votes
0answers
26 views

Calculate Distance between 2 Shapely Points in Meters

I am trying to calculate the distance (in meters) between 2 lon/lng points of type shapely.geometry.point.Point using Python and Shapely. These 2 points are returned from PostGIS by SQLAlchemy/...
2
votes
1answer
28 views

Change PostGIS Column from Geometry to Geography

I have a PostGIS table with a column of type Geometry(POINT) and SRID 0 that must be changed to the type Geography(POINT) as it will be used to store lat/lon values. How can we make this alteration to ...
0
votes
1answer
15 views

Multiplying rasters by rasters within PostGIS

I have a situation where I have to multiply sets of rasters by each other. Currently I have subfolders of rasters on my machine and am looping through them and multiplying them with rasterio. Is there ...
1
vote
1answer
24 views

Function st_clip(raster, integer, geometry) does not exist

I am trying to replicate this example by alphabetasoup for summarizing pixel values according to census polygons, but am getting this error. ERROR: function st_clip(raster, integer, geometry) does ...
3
votes
0answers
20 views

Adding points to every “eligible” cluster in PostGIS (ST_ClusterDBSCAN)

I'm currently using ST_ClusterDBSCAN in PostGIS to identify similar points for conflation. (Using clustering to see where multiple entries are within a given range and then manually reviewing & ...
2
votes
1answer
67 views

Calculate 500m south of coordinate in PostGIS

I have a point layer punkte_test. From that I want to make a line: first point of the line is the point A from the point layer itself. Second point of the line shall be y(B) = y(A) - 500 m . How can I ...
0
votes
1answer
25 views

python - psycopg2.errors.RaiseException find_srid() - could not find the corresponding SRID

I'm trying to import a layer into a PostGIS database with gdf.to_postgis('test_polygons', engine, if_exists='append', index=False, dtype={'geom': Geometry(geometry_type='POLYGON', srid= 4326)}) And I'...
2
votes
1answer
31 views

Is this ST_Relate result expected?

The following PostGIS query involving ST_Relate gives FFFFFF102 as its result: SELECT ST_Relate( ST_GeomFromText('POLYGON EMPTY'), ST_GeomFromText('MULTILINESTRING((0 0,0 1),(0 1,1 1,1 0,0 0))'), ...
0
votes
0answers
16 views

how to use PostGIS functions with geometry(MultiPolygon)? [closed]

org.geotools.jdbc.JDBCDataStore stores a shapefile in a PostgreSQL database as a geometry(MultiPolygon). I am trying to use PostGIS functions such as ST_Contains on the geometry(MultiPolygon) but it ...
2
votes
0answers
28 views

GeoJSON format (Geography) for posting in Postman tin PostgreSQL

I am trying to do an update posting on Postman for the first using PostGIS / Geography file type and I don't know where I am going wrong. I have included the model below of the table in pgamdin 4 / ...
2
votes
0answers
11 views

How to use WHERE IN clause with ogr2ogr [duplicate]

I am exporting some tables from PostGIS to a geopackage. My command to do is task is as follows ogr2ogr -append -f GPKG E:\workspace\test.gpkg PG:service=XXXX -where app_einzugsgebiet.hauptgemeinde=...
0
votes
1answer
15 views

Querying PostgreSQL DB with Google “get.bounds()” coordinates

I am trying to only display markers on a Google Map that are in the current map bounds and then update the markers when the map moves. The difficulty I am running into is querying my database to pull ...
1
vote
1answer
47 views

Split line across multiple polygons preserving order

I have polygons stored in the database as (Multi)Polygons using PostgreSQL (13.1) and PostGIS (3.0.2) with a srid of 4326 (aka states). A user generated line (LineString) (aka route) will be used to ...
0
votes
1answer
23 views

Can I use a SQL View as an ImageMosaic Store index in GeoServer?

I have an application server creating rasters from the vector data it receives periodically. The data contains either "real" measurements or forecasts about a specific weather phenomenon, ...
0
votes
0answers
14 views

Postgresql POSTGIS not using index field while having GIST index on geometry field [duplicate]

I have around 2M rows on my PostgreSQL table with GIST index on geo_point field but while I try to use ST_Distance or ST_DWithin it is not using the index instead it is doing Parallel Seq Indexed ...
1
vote
0answers
29 views

PostGIS difference between polygons - Ring Self-intersection?

I want to take ST_Difference or ST_Intersection to take the difference between two polygons. SELECT a.id, a.geom, st_difference(a.geom, b.geom) FROM liechtenstein.flaechen_norden a, liechtenstein....
1
vote
1answer
49 views

PostGIS function st_makevalid crashes the PostgreSQL Server [closed]

When I run my code running on PostGIS version 2.5, I don't get an error but when I run it on 3.1 it the server gives an error and enters the recovery mode every time. pgadmin4 messages:(*"...
1
vote
0answers
29 views

Displaying dynamically changing data layer in moving view using OL-Cesium [closed]

I'm developing an application that contains a tilted map based on OL-Cesium. The "camera" height is very low (zoom level is the highest), and the view is "moving" continuously, ...
3
votes
1answer
58 views

PostGIS multiple spatial joins in the same query

While I understand how I would do something like this in non-spatial SQL, I have confused about how I would join data from more than two joins in one query in spatial SQL. I have a table of zipcode ...
0
votes
0answers
16 views

How to insert the result of ST_Intersection into another column when the result has multiple geometry types [duplicate]

In PostGIS I have three tables table1, table2 and table3 of same type: CREATE TABLE tableX ( myid integer NOT NULL, wkb_geometry geometry(LineString,4326) ) table1 and table2 are populated. I ...
1
vote
0answers
35 views

shp2pgsql available in postgis:13-3.1-alpine but no in postgis:13-3.1 docker image

From the postgis/postgis:13-3.1-alpine docker image I can push 3 shapefiles into my database using shp2pgsql in an init script. But from the postgis/postgis:13-3.1 image I cannot: (...) db_1 | /docker-...
1
vote
0answers
36 views

Understanding the differences in PostGIS full version string from two Docker images

I have to migrate a PostGIS database from mdillon/postgis:11 to postgis/postgis:13-3.1. From mdillon/postgis:11 docker image I can read: # select postgis_full_version(); ...
1
vote
0answers
31 views

Should I use geometry type over geography type when usually expecting back a lot of rows?

I am in the planning stages of a service that will do a fair share of searches for items in a geographical database. I will have to use PostgreSQL and PostGIS for that purpose and have learned so far ...
0
votes
1answer
14 views

org.geotools.jdbc.JDBCDataStore: add information that is stored with the shape file?

Is there a way to add metadata such as file name and file creation timestamp as separate table columns in the table when org.geotools.jdbc.JDBCDataStore stores a shape file in a PostgreSQL database?
1
vote
1answer
44 views

Convert Esri file gdb to PostGIS

I am trying to convert Esri file gdb to PostGIS I created a new database : 'newkelibia' with pgAdmin 4 using OSGeo4W shell I executed this : ogr2ogr, -f "PostgreSQL" PG:"host=...
1
vote
1answer
14 views

org.geotools.jdbc.JDBCDataStore stores shape files in the database but files stored in own tables

Using geotools to store shape files in a PostgreSQL database. Each shape file is being stored in it's own table in the database. Is there a way to store the files in a single table? Also, the tables ...
0
votes
0answers
16 views

Missing shp2pgsql command on postgis docker installation, when I use postgis:postgis:12-3.1 image?

I'm installing a docker container from this image, taken on Docker Hub : postgis:postgis:12-3.1, and according to posts on Stackexchange I did a service reload after the container initialization. But ...
6
votes
0answers
72 views

Saving ArcMap/ArcGIS Pro symbology to a PostGIS DB for each layer

I want to have a default style/symbology assigned to each layer in a PostGIS database so that whenever it is loaded in ArcMap/ArcGIS Pro, it will come up with the default style. There is a way in QGIS ...
3
votes
1answer
29 views

Point in multiple circles in PostGIS

My use case is one of defined service area or the inverse of the nearest neighbor query. I have a database of service providers each willing to work within N miles (radius) of a point. On the flip ...
1
vote
1answer
33 views

ST_Line_Substring doesn't work

I want to split some line strings using ST_Line_Substring. My basic query works: SELECT ST_LineLocatePoint(b.geom_way,ST_ClosestPoint(a.geom,b.geom_way)) as Loc_Point FROM splitpoints a CROSS ...
1
vote
0answers
61 views

OSRM Contract with multiple input OSM files stalling

I received multiple shapefiles for USA states map data split into different groups of states. Approach - 1: I can't merge multiple shapefiles and merge into a single .osm file because it's exceeding ...
4
votes
0answers
84 views

Upgrading from PostGIS 2.5 to PostGIS 3.1 - calling two set returning functions in a UDF is 7x slower

I'm migrating our PostGIS 2.5 stack to PostGIS 3.1. In general it's been really smooth - a few of our operations on larger geometries are much faster, and the rest of our performance is about the same....
0
votes
1answer
29 views

Joining and Updating PostGIS data using QGIS

I have a PostGIS database with which I am currently using QGIS to manage the layers. Say I have a layer in PostGIS that I want to do a join to to add new data in. Is it possible to do this join in-...
3
votes
2answers
65 views

Using ST_SquareGrid to create grids

In PostGIS 3.1 the function ST_SquareGrid(size, bounds) generates a grid. The documentation for the functionality is available in the docs. Understanding how this is done is not clear from the ...
7
votes
2answers
63 views

ST_Overlaps in return TRUE for adjacent polygons - PostGIS

I'm trying to find overlapping polygons with ST_Overlaps function. Everything works fine but there are cases when ST_Overlaps returns TRUE for adjacent polygons. Example: POLYGON((145053.95 167055.18,...
2
votes
1answer
64 views

Merging polygons using PostGIS

I need to do a simple merge polygon in PostGIS. The attribute table from both input shapefiles needs to be preserved. This is easily done In QGIS by using the merge polygon tool. But I don’t know how ...
3
votes
0answers
89 views

Getting active PostgreSQL connection username when stored through authentication configuration in QGIS 3.16

How do I get the username on the current layer PostGIS in QGIS 3.16.2-Hannover? We tried examples: Getting active PostgreSQL connection username when stored through authentication configuration in ...
2
votes
1answer
191 views

Slow retrieve PostGIS raster value through ST_Value

I have a PostGIS DB containing a set of daily raster data from 1981 up to 2021. The inserted raster tile size is 83x90. Problem: I wanna query the data through ST_Value for a specified Lat Lon, ...
0
votes
1answer
17 views

Failing to load OS .gz files into QGIS 3.16 using OS Translator II

I'm new to QGIS and not strong on writing scripts or code. That said I've installed QGIS 3.16, PostGIS 3.1.0, PostgreSQL 13 and also OS Translator 2 and followed the configuration guidance at https://...
0
votes
1answer
60 views

PostGIS : Find direction between custom GPS tracks (linestrings) intersected with pgr_dijkstra route

I have many custom GPS tracks(linestrings) and a route generated by pgr_dijkstra by giving source & destination as input. Using ST_Intersects, I able to filter required GPS tracks belongs to the ...
6
votes
1answer
83 views

PostGIS Voronoi Polygons with extend_to parameter

I am creating voronoi polygons for locations inside a country, using PostGIS. The function creates these areas for ~80,000 locations in less than a second. However, I can only get the polygons to ...
1
vote
1answer
25 views

Populating a table that uses a different geometry from another table of a different geometry

Does anyone know how I can populate a table that uses a different geometry from another table of a different geometry? (example geometry(points) -> geometry(Linestring) or geometry(polygon) I'm new ...
3
votes
1answer
68 views

How to dynamically query and visualize postgis data using GeoDjango and Openlayers?

I am trying to implement dynamic query functionality in GeoDjango and OpenLayers. First, I filter the data and return it in GeoJSON format using Django serialize. Then, the return GeoJSON data ...
0
votes
0answers
9 views

How to create a PGDUMP from LVBAG format using ogr2ogr?

I am trying to figure out how to use this GDAL driver to create a SQL dump, using the ogr2ogr commandline. I have tried the following: ogr2ogr -oo AUTOCORRECT_INVALID_DATA=YES --config PG_USE_COPY=YES ...
1
vote
0answers
18 views

Can't upload raster file on PostGIS

I've been trying to upload my satellite imagery which is 1.5 GB to my PostgreSQL database, using the code below, the error for each command is stated below; raster2pgsql -s 4326 -t auto -F -I -C -Y '....
0
votes
0answers
12 views

PostGIS geom line string to display in Django map

I would like the WKB PostgreSQL geom data (extracted from a shapefile) in the below table to display in a django web app. I have previously successfully mapped points to a Django web app from a ...

1
2 3 4 5
158