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
17 views

Error publishing PostGIS layer to GeoServer using Python gsconfig library

I am unable to publish a PostGIS table as a GeoServer layer using the gsconfig library. I was able to create a workspace, data store but was unable to publish layers in the store. While running the ...
0
votes
1answer
32 views

Does repeated coordinate re-projection cause a loss in accuracy?

If I take a set of GIS data (shapes, points, lines, etc) and convert the projection many times, will there be a loss in accuracy in the data? The reason I ask is that I have a dataset from OSM which ...
1
vote
1answer
12 views

Reading/writing PostGIS layers with qgis_process-qgis command line tool

How do I use the qgis_process command line tool with PostGIS layers as input and/or output?
0
votes
0answers
23 views

ST_UNION if buffered polygons overlaps or intersects

I'm trying to ST_UNION buffered polygons through the following line of code select ST_DUMP(ST_UNION(ST_TRANSFORM(ST_BUFFER(the_geom::geography, 100,'endcap=square')::geometry, 3857))) from table at ...
3
votes
3answers
191 views

Select polygons within polygons in same layer

I have a buildings layer that actually has a flaw. The inner courtyards, drawn as another polygon within the building polygon, should be islands of the building polygons. Instead, they are other ...
0
votes
1answer
24 views

Division of polygon into irregular shape and varying area of polygons

I have a major polygon and with its area, I want to divide this polygon into any numbers of sub polygon. I have also area of sub polygon and number into which major polygon is divided. I want to ...
1
vote
1answer
27 views

PostGIS: geometric shapes crossing 180 meridian

How to deal with a problem of intersection of geometry shape (linestring, polygon) and 180 meridian? I have polygons distributed all over the Earth globe, some of them intersecting 180 meridian. The ...
2
votes
0answers
19 views

MapServer / PostgreSQL don't display an ITEMQUERY

I try to search a string in MapServer with the method ITEMQUERY from a PostgreSQL table,. It look very simple but I receive the folow error: msPostGISLayerWhichShapes(): Query error. Error executing ...
0
votes
0answers
44 views

st_intersects in where clause returning inconsistent data

In our application we are performing a bounding box search with incoming bounding box coordinates we are converting to a polygon. In our database we are having existing locations as point in a table ...
0
votes
1answer
33 views

Polygons with same geometry and bbox but can't be aggregated with GROUP BY

I'm trying to create a constraint unique on a geometry column to avoid duplicate ones, following https://dba.stackexchange.com/questions/299098/why-doesnt-my-unique-constraint-trigger/299107#299107 ...
0
votes
0answers
21 views

Intersection of 3d layers in PostGIS with SQL Queries

I am currently working on a project for Uni with PostGIS and SQL Queries. I am trying to calculate the intersection between two 3d layers. Based on online tutorials I came up with this query. SELECT ...
1
vote
1answer
53 views

Storing Google Maps API returned polyline in PostgreSQL (or PostGIS)

By making Google Maps API call, I received a response JSON which contained this polyline: ezdvBcps}Lt@W@F@BVKl@UK]Wc@}@kAOKBEzA{BdA}Ab@s@LBf@RjARZBv@?rAAfBIX?f@FLBf@^b@hATbAF~@BvB?hALjBHpCLjAVdAd@...
2
votes
1answer
49 views

PostGIS - Polygon crossing latitude 0 and longitude 180

I need to find all the couples long/lat (listed in a table) that belong to the area defined by a polygon in PostGIS (version 3.0). My area is defined by the following polygon, which crosses the ...
4
votes
1answer
27 views

Update array geometry field using array lat & long field in PostGIS

I have two array columns in Postgres named lat and long. Both have 1-5 values in their array field. Like the following Now I need to create geometry from each value based on the array index of lat &...
4
votes
1answer
48 views

How to reformat SQL statement to export GeoJSON from PostGIS table?

So, I realize this question gets asked a lot, but the root of my question is related to the syntax of the SQL statement used to format the result table as GeoJSON. I am currently able to execute a ...
1
vote
0answers
55 views

PostGIS - Area inside or outside w.r.t. a polygon

I need to check if a specific point belongs to the area defined by a polygon on a map using PostGIS. How can I specify if the area to be considered is either the one "inside" the polygon, or ...
1
vote
0answers
33 views

Foreign Data Wrappers

I had created a foreign server in the E drive with a data source of "e:/files/" for Map info tab files. It was all working well until I had to refresh or re-create the server. It gave an &...
-1
votes
0answers
28 views

Selection of neighboring buildings of different heights [closed]

I am working on a layer of buildings indicating the height of these. I am looking for a method that would allow me to select only contiguous (or neighboring) buildings of different heights (see 1 and ...
4
votes
3answers
167 views
+50

Calculating shortest route by road from one origin point to multiple destination points at once using PostGIS (pgrouting)

Referring to the below snapshot what is the best possible way to find the road distance from the red star node to all of the yellow nodes using PostGIS. Note: I don't have the road distance data ...
1
vote
1answer
35 views

Cannot connect to PostGIS database with GRASS, but can connect with psql with the same credentials

I am trying to connect to a PostGIS database (hosted in a docker container) on a remote server with GRASS. I am able to connect with psql and confirm that the table is there (IP redacted): psql -h x.x....
0
votes
0answers
17 views

Setting CRS/SRID of raster tables referenced in raster_columns view in PostGIS database

I imported some DEMs to my PostGIS db and two have unknown CRS (maybe because their CRS code is Esri and not EPSG). Do I update the raster_columns view or do I directly set/assign the CRS of the said ...
3
votes
1answer
35 views

Add newly created columns to an existing PostGIS table from GeoPandas?

I am trying to add some newly created columns to an existing table in PostGIS table from GeoPandas. Previously, I've pulled the data out of my database, run geocoding, and then created new columns ...
1
vote
0answers
33 views

Reduce time while displaying large geom and merging it using st_union

I have multipolygon geom data which is very large in size and also takes a lot of time to display the data on the map and sometimes even in tables in pgAdmin. The approach I have been trying is ...
1
vote
2answers
66 views

Setting CRS/SRID using the to_postgis function with GeoPandas?

So, I'm trying to update a PostGIS table after doing some reverse geocoding and other column manipulation I'm running into the find_srid() - could not find the corresponding SRID error. Based on my ...
1
vote
0answers
49 views

Different calculation results of an area between OpenLayers and PostGIS [closed]

In OpenLayers I draw a 5 meter * 5 meter rectangle. If I calculate the area of the geometry in OpenLayers I get 25 m^2. But in PostGIS I'm getting 25.04. I need to get the same result. OpenLayers: ...
2
votes
1answer
40 views

Cannot load WFS via QGIS into PostGIS

I try to load this WFS https://fbinter.stadt-berlin.de/fb/wfs/data/senstadt/s_lor_plr_2021 into PostGIS using QGIS. However, I always get the following error Error: 6 Object writing error: Object ...
1
vote
1answer
32 views

Geopandas to_postgis function returning no valid geometries error with table that originated in PostGIS

So, I apologize in advance as there's probably better ways to approach what I'm doing and I'm new to Python. I'm trying to update a PostGIS table with newly appended columns from reverse geocoding and ...
4
votes
1answer
56 views

Gaps and Islands problem in PostGIS for GPS traces

I'm using ST_MakeLine to create traces of ship movements. The query below yields all the GPS points on a certain day, and I'm using ST_Within to only find ships in the navigable portions of the river. ...
1
vote
2answers
97 views

How to create when possible a rectangle in polygons with holes [duplicate]

I did not find a similar question on this site. Is there a way to automatically create a rectangular polygon of 500 square meters inside another polygon with rings using QGIS? (I have 28,000 polygons ...
0
votes
0answers
28 views

Filter by field value in a child table

In QGIS, I would like to filter the display of a parent table in (a postgis point theme) by the values in a field from a child table (a postgresql table in the same db). The parent table and child ...
4
votes
1answer
92 views

Shortest paths between points over multiple linestrings

I'd like to calculate the distance between one and multiple other points along a network of LineStrings. I'm aware of this question which calculates the distance on a substring of a LineString, but ...
1
vote
1answer
33 views

Intersection of corine landuse cover and grid cells to get the distribution of classes for each grid cell

TLDR: How to assign class distribution of landcover classes to grid cells. I'm struggling to find an efficient way to intercect corine landuse cover with my 100x100 m grid to get the distribution of ...
1
vote
0answers
27 views

Left join on Postgresql and QGIS returning different results?

I'm using the following expression to join to layers and produce a virtual layer. select * from table1 left join table2 on table1.field1=table2.field2 When I do it in Qgis, my virtual layer doesn't ...
1
vote
0answers
38 views

Why is ST_ClusterDBSCAN assigning distant points to the same cluster?

I have around 5 million points that I want to cluster. I'm having trouble achieving both finding correct results and also doing so within a reasonable amount of time. I have described this problem ...
0
votes
1answer
19 views

Adding Z dimension to 2D postgis table

Is it possible to add a z dimension to an existing 2d postgis table for future records without having to create a new table?
1
vote
2answers
40 views

Create one PostGIS connection in QGIS with multiple databases from one host?

I have a number of spatial databases each containing multiple schemas on one server, I want to use QGIS to examine layers, Is it possible to create one PostGIS connection in QGIS and add all databases ...
1
vote
1answer
78 views

EPSG:3857 units are in meters, why is ST_Distance showing distant locations to be less than a meter apart?

According to Google, these two points are about 137 meters apart, yet the result of ST_Distance reports just a fraction of a meter? The units of the result should be in meters, as I understand it, ...
-1
votes
1answer
68 views

PostgreSQL/PostGIS database in OneDrive?

Is it possible to install a PostgreSQL/PostGIS database in an OneDrive-Cloud and access it with QGIS?
7
votes
2answers
89 views

Plot a point at the boundary of all polygons facing the nearest road line in PostGIS

I want to add/plot a point geometry at the boundary of all home polygons. Conditions: The point should be facing the road geometry. As it will considered as the approximate entry point for that ...
0
votes
0answers
15 views

Import only Point features with osm2pgsql | Import only certain Tags of point features with osm2pgsql

I am very new osm2pqsgl. I have downloaded a osm.pbf file for all of Europe and I want to add this data to my Postgres database. However, I am only interested in points, no linestrings nor polygon, ...
2
votes
2answers
99 views

Speedup PostgreSQL views with QGIS layers

SETUP I have an ever growing read-only geometry table of many point events (+1.2M). This db table has corresponding spatial indices to speed querying up & was also spatially partitioned by geo-...
0
votes
0answers
56 views

Importing shapefile in PostGIS DB

I am trying to import a shapefile (created using QGIS) into my PostgreSQL DB with PostGIS extension using SQL. I got the message below, any body can help: Importing with configuration: transects, ...
6
votes
2answers
72 views

Making multipoint column with at least 2 points

I have a table called my_table that has a MultiPoint column. I want to limit the MultiPoint column. A Multipoint must have 3 Points most and it must have 2 points at least. How can I do the limitation ...
0
votes
0answers
28 views

Convert geometry type multilinestring to polygon

I want to create a .sh script which would edit my JSON file. "features": [{ "type": "Feature", "properties": { "value": 1.0, &...
0
votes
1answer
36 views

Object in 100 m distance, Error: Operation on mixed SRID geometries

I perform the following query: SELECT * FROM "houses" WHERE (ST_Distance(coordinates, 'POINT(-0.374930 39.478400)'::geometry) < 100) To find houses around 100 meter distance from: 39....
0
votes
0answers
34 views

Troubleshooting ogr2ogr error when loading shapefiles into PostGIS: Error 1: Couldn't fetch requested layer

I am trying to get familiar with PostGIS. I have been trying to work through the PostGIS tutorial and following the instructions closely but they aren't working for me and I'm not sure how to ...
2
votes
1answer
25 views

PostGIS equivalent of Pandas geometry.area for square feet

I have a Python script that has a line that takes the geometry and calculates it into a square feet value by multiplying the area by 10.7639 in a geopandas dataframe. bldg_feat_poly_df['bldg_sqft'] = ...
1
vote
1answer
34 views

How to generate hexagon polygons with coordinates of the center point in PostGIS?

I have given a database with coordinates and want to use these coordinates as the center points for hexagon polygons. PostGIS provides this convenient function: ST_Hexagon The example from the ...
1
vote
0answers
46 views

PostGIS table visualization in QGIS without download permission

I'm used to work with PostGIS databases and accessing them from QGIS. I know how to create schemas or grant permissions, but I was wondering if it is feasible to set a user profile that could "...
1
vote
0answers
35 views

How to change the geometery type multilinestring to polygon in a JSON file through og2ogr [closed]

I have have a JSON file of earthquake data. The data type within the JSON file is a MULTILINESTRING. For my further workflow I need the data type POLYGON. I am able to import the dataset as POLYGON ...

1
2 3 4 5
169