Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
8,423
questions
0
votes
2answers
26 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
16 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
0answers
15 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 ...
1
vote
0answers
9 views
MapServer / PostgreSQL dont 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
40 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
31 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
20 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
42 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
47 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
45 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
52 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
30 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 ...
2
votes
3answers
135 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
34 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
65 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
30 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
55 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
88 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
66 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
54 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
70 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
31 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
34 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 ...
2
votes
1answer
84 views
Split polygon with West East direction with equal area in PostGIS
This code splits into NS direction but I need to split with WE direction (horizontally):
WITH foo AS (
SELECT id,name,geom, blade
FROM admin4
CROSS JOIN LATERAL ( SELECT ST_xMin(geom) + (ST_xMax(geom) ...
0
votes
0answers
63 views
GeoServer PostGIS Store Connection problem - Cannot see database
GeoServer => 2.20.1 (Jetty)
PostgreSQL => "PostgreSQL 13.3, compiled by Visual C++ build 1914, 64-bit"
PostGIS => "POSTGIS=""3.1.2 3.1.2"" [EXTENSION] PGSQL=...
1
vote
1answer
29 views
Import a raster into PostGIS and band's max&min values have changed
I have NDVI raster data from the MOD13Q1 product. I have uploaded it into the PostGIS using raster2pgsql -s 3857 -I -c C:\Users\xxxx\MOD13Q1.A2013001.NDVI.crs3857.tif public.ndvi_2013001 | psql -h ...
4
votes
1answer
65 views
Joining 3 tables (Points, lines and Polygon) in SQL
I am trying to ask two questions in one query using SQL.
I want to know:
a) which polygon (ward) a line (road) is in.
and then
b) within that ward, which points (B&B's) are within 1 mile from the ...
