Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
8,278
questions
4
votes
1answer
61 views
ogr2ogr cannot import into table with two geography columns
I am using ogr2ogr to import geojson files into a PostGIS table. The table has two geography columns ("footprint" and "centroid"). The geojson files contain polygon features, and I ...
3
votes
1answer
113 views
PostGIS, closest point to another point
I have two tables: point1 and point2. For each point from table point1, I want to find the closest point from table point2.
For example: For point with id=1 from table point1, the closest point from ...
2
votes
1answer
63 views
Error when altering a table column using ST_Dump
I user ALTER TABLE to change a Multilinestring geometry column to a Linestrings geometry column with this syntax:
ALTER TABLE jalan_diy
ALTER COLUMN geom TYPE Geometry(LINESTRING, 4326)
USING (...
4
votes
1answer
85 views
Getting the 'composite' of two polygon layers
I have two table of polygons, and I want to 'flatten' them into one table, and have the result tell me which areas are in one table of polygons, which in the other table of polygons, and which are in ...
2
votes
1answer
45 views
Reloading attributes of selected item using GeoServer/OpenLayers
I have a PostGIS database with items whose geom is an ST_Point, so they show as a clickable point when shown in GeoServer/OpenLayers. Each of them has around 90 attributes and the database is ...
1
vote
1answer
55 views
St_buffer then reverse st_buffer
A slightly theoretical PostGIS question. If you have a polygon, perform a positive buffer on it, then perform the same buffer operation but negative, you end up with a slightly different polygon than ...
1
vote
1answer
40 views
ST_Segmentize - segmentation and duplicates
I did a segmentation on a layer of lines and then I transformed this segments to point dividing the line in 5 parts indepentently of his lengh. The problem I have, is that some of them, insted of ...
3
votes
1answer
43 views
Add Z value to an existing 2D point in PostGIS
I'm facing this query which is used to extract a centroid point (3D) of a 3D polygon resulting from the cartographic intersection of a 2D polygon given by the user of an application (which is inserted ...
2
votes
1answer
55 views
PostGIS sfcgal ST_3DArea
I want to compute surface areas of a large CityGML data set. However, I ran into some issues, which I could not resolve or fully understand.
The error message is the following:
Polygon is invalid : ...
2
votes
0answers
59 views
PostGIS trigger to create line in one table when line is created in second table
I am trying to migrate to PostGIS from shapefiles and I've started setting up the trigger inside.
I manged to create trigger which are populating all of the geometry fields (length, Cartesian ...
2
votes
1answer
46 views
PostGIS: Automatically updating geometry field in a table after updating lat long values
It's simple to create a view from a table that generates a valid geometry field:
create view mywgs84points as
select row_number() over (order by id) as gid,
st_setsrid(st_point(lon, lat), 4326)
from ...
3
votes
1answer
78 views
PostGIS: Does re-creating a spatial index after inserts or updates affect performance?
Creating a spatial index on a geometry field is very important. The docs also state that you should perform a VACUUM ANALYZE after large update operations. But is it ever necessary to drop an existing ...
3
votes
1answer
107 views
PSQLException: ERROR: operator does not exist: public.geometry && public.geometry
I have database with PostGIS already installed. I am running the following query from pgAdmin and I am successfully able to run it without any errors.
But when I run it with Java, I am getting the ...
5
votes
1answer
114 views
ST_ Intersects vs ST_DWithin behavior on polygons using PostGIS
I have a large table with millions of current and historical building footprint polygons and need to efficiently fetch all of these that fall fully or partially within a user specified set of search ...
3
votes
1answer
54 views
PostGIS generic vs specific geometry types
PostGIS expects a geometry type parameter when creating a Geometry column and permitted values include GEOMETRY, POINT, POLYGON, etc.
If I choose GEOMETRY then my Geometry column can store points, ...
2
votes
1answer
23 views
PostGIS: Aggregate small raster to larger raster
I am just getting into rasters in PostGIS and I have stumbled across a problem I can't solve:
Given one large raster A (e.g. pixel size 5km x 5km) and one smaller raster B (e.g. 1km x 1km) which might ...
2
votes
1answer
32 views
Perform spatial functions on features from WFS in QGIS
I have a layer coming from a WFS that itself gets its data from a PostGIS database via GeoServer.
As of the capatbilities of the service I can perform a Crosses operation in oder to select features ...
3
votes
1answer
36 views
Testing within MULTIPOLYGON when borders touch in R
I am new to geodata so maybe this is a silly question.
I have two multipolygons and what I want to do is to test in which of the polygon of "constituencies" each polygon of "wards" ...
0
votes
0answers
37 views
Extract all cities with lat lon coords from Slovakia OSM using PostGIS
I need to extract all cities with lat lon coords from Slovakia OSM.
This is how I imported OSM file downloaded from Geofabrik:
osm2pgsql --slim --username postgres --database gis --hstore slovakia-...
2
votes
1answer
71 views
PostGIS TopologyException error when all polygons pass ST_IsValid test
I am getting this PostGIS error:
ERROR: lwgeom_unaryunion_prec: GEOS Error: TopologyException: Ring
> edge missing at 577690.01999999955 213605.69999999925 SQL state: XX000
I have checked that ...
0
votes
1answer
49 views
ST_azimuth creates only NULL values for Virtual Layer in QGIS
I have two layers with points representing two measurements of the same structure using two different methods. The points representing the same structure element have the same ID in both layer.
To ...
3
votes
1answer
60 views
Get bounds as GeoJSON
I need the coordinates of the bounding box as a GeoJSON from PostGIS.
To do the calculations on the PostgreSQL server I use:
SELECT ST_Extent(l.position_geo) AS box (...)
The answer is as expected a ...
0
votes
0answers
62 views
Using min/maxscaledenominator rule in GeoServer to filter data from heavy layer served as WMS
I have a heavy multiline layer (>1.5GB) saved in a PostGIS database, connected to GeoServer 2.14 and being served as WMS to be displayed in a Leaflet map.
Right now, the layer is shown correctly in ...
2
votes
1answer
66 views
Why is pgr_dijkstra not giving shortest path
I am using a road network and the following postgres/postgis operations to perform routing using pg_routing and the pgr_dijkstra method. I am successfully generating routes but they are not the ...
2
votes
0answers
56 views
Should TOAST compression be disabled for PostGIS?
From this post about Binary data performance in PostgreSQL, I read that storing (large) binary data can be very slow in PostgreSQL (compared to storing the same data in file systems). This seems to ...
3
votes
1answer
43 views
Does UNION in PostGIS check for distinct geometries, or only other fields?
I've been getting some funny behaviour from UNION vs. UNION ALL when combining two tables in PostGIS, so just wanted to check:
Does UNION include a check for duplicate geometries when determining rows ...
2
votes
0answers
26 views
Converting a PostGIS grid back to raster
This is a follow-up question (or inverse) of the question
Converting a PostGIS raster back to vector.
I am trying to create a workflow that converts a PostGIS raster into a regular grid of polygons, ...
1
vote
0answers
22 views
How do I overlay one raster over another
I have two rasters on a postgresql database one is a map of the world and the other looks like a weather map. I have been trying to figure out how to overlay one map on top of another.
What I have ...
2
votes
1answer
48 views
Use index of geometry inside user-defined function
create function are_close_to_each_other(g1 geometry, g2 geometry)
returns boolean
language sql immutable as $$
select ST_DWithin(g1, g2, 100);
$$;
select x.*, y.*
from sometable x, sometable y
...
0
votes
0answers
36 views
Schema error while uploading raster in PostgreSQL
Tried many ways
A.
import os
import subprocess
from subprocess import PIPE, run
db_name = 'agro_dss'
db_host = 'localhost'
db_port = '5432'
db_user = 'postgres'
db_password = 'imd123#'
os.environ['...
1
vote
0answers
34 views
OSM tile server -- too many highway labels
I'm running a local OSM tile server. I've combined three pbfs (three neighboring states) using osmconvert, then loaded them into postgis database:
osm2pgsql --slim -d gis --hstore --multi-geometry --...
4
votes
1answer
71 views
How to count the number of 100m length chunks in a table with line geometry?
And by chunks I do not mean segments so I can't use st_segmentize for this
In other words I have a linetable which has many lines of varying length. Example:
id | st_length(geom) | intended count for ...
2
votes
2answers
116 views
Difficulty connecting QField to connect to PostGIS
I've been working on setting up a PostGIS server for QField for the past week, today I almost thought I had it working but now QField keeps throwing me one error no matter what I do:
"Connection ...
3
votes
1answer
79 views
Determine if approximate count of features in polygons is less than 100_000
Using PostgreSQL 12.
I have a table of polygons, a table of points, and a table of lines. All tables have GIST indexes on the geometry column. There is a view that includes both the lines and points, ...
3
votes
2answers
104 views
Showing PostgreSQL table comments in ArcGIS Pro?
Comments are a useful way to annotate database objects such as fields and tables in PostgreSQL. A comment on a table is written like this:
COMMENT ON TABLE table IS 'QGIS has no issues reading table-...
1
vote
1answer
74 views
Generating new UUIDs (primary keys) while importing from QField breaks (1:n relation) to child features
We have a QGIS project with a parent (parquets) and two child layers (peuplements and gestion) (1:n relation) all stored in PostGIS. The parent primary key is a UUID (no default value defined). Both ...
0
votes
0answers
23 views
MBTiles to PostGIS
I have an MBTiles file /database containing aerial imagery.
Does any one out there know of a way to store a MBtiles database into a PostgreSQL/PostGIS database?
2
votes
0answers
56 views
Select lines in within a polygon (buffer) with PostGIS
I have one polygon layer "polygons" and one line layer "lines". I would like to create a layer with all line parts that are inside the polygon layer.
First I cut the lines in the ...
0
votes
0answers
73 views
SQL Server error when creating Geography data from a Multipolygon Geometry
So, in SQL server, I have a table called Municipios, which contains a column with 2463 entries of geometry type, with their SRID set to 4326.
I'm running the following Query to try to convert them to ...
0
votes
0answers
10 views
visualising edge_graph from list pgr_dijkstra graph [duplicate]
I am creating a query that finds the shortest path from dangerous crossing nodes to hospitals (many-to-many). I have the proper output from pgr_dijkstra, with output as follows
seq | path_seq | ...
0
votes
0answers
27 views
visualising edge_graph from list pgr_dijkstra graph
I am creating a query that finds the shortest path from dangerous crossing nodes to hospitals (many-to-many). I have the proper output from pgr_dijkstra, with output as follows
seq | path_seq | ...
1
vote
1answer
26 views
OSTN15 transform not working in postGIS build
I have an issue with my new (CentOS) postGIS setup not transforming WGS84 to BNG via OSTN15 correctly. It all worked beautifully in postGIS2 with proj4.8. As recommended, the relevant entry is in the ...
2
votes
1answer
32 views
Max Zoom in GeoServer
I don't find a way to see the 11 points of my set of data that are just meters from each other as separate points in GeoServer and I don't know why. I would have assumed that GeoServer finds the ...
2
votes
1answer
39 views
Find nearest neighbour for set of points [duplicate]
Using PostgreSQL and PostGIS. I have two sets of point data (geoms), one is a node graph indicating roads and the other represents street crossings. For my set of crossing data I want to find the ...
2
votes
2answers
88 views
How to reproject a table using PostGIS
To reproject a table into different CRS is a very common practice in geospatial analysis. However, I am still struggling to reproject my table in PostGIS, after reading loads of Q&A related to ...
1
vote
2answers
133 views
Change in coordinate precision with ogr2ogr moving from GDAL 2 to 3
I'm migrating some old code from GDAL 2 to GDAL 3. I use ogr2ogr to import a shapefile into PostGIS like this:
ogr2ogr -f PostgreSQL -nln mwe PG:"host=localhost user=myuser" mwe.shp -...
2
votes
1answer
34 views
Constructing spatial queries that include data from non-spatial tables in PostGIS
I've got a database with the following tables
CREATE TABLE survey (
id_s integer primary key,
eventdate timestamp without time zone,
locality character varying(100)
geom geometry(Point,25830))
CREATE ...
1
vote
1answer
79 views
Processing script to load vector layer from PostGIS - connection string error
I'm trying to build a processing plugin to directly load layers from PostGIS database where the user can define some parameters to import the right layer. Here is the code I'm working on :
from qgis....
0
votes
0answers
89 views
QGIS ERROR: invalid input syntax for type uuid: "uuid_generate_v4()"
I have a problem to get parent child relations to work properly in QGIS.
I have a parent table as polygon and two linked tables a non-spatial child_1 table and a second child_2 Point layer.
When I try ...
0
votes
1answer
40 views
PostGIS compilation issue with PostgreSQL 14
I am trying to compile PostGIS 3.1.1 from source under the newly released PostgreSQL 14 beta1. I downloaded the latest PostGIS source and then compiled it under Ubuntu 20.04 LTS as follows:
tar xf ...