Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
8,676
questions
0
votes
0
answers
57
views
Error 6 when importing data from QGIS to PostGIS database ERROR: value too long for type character varying(10)
I collected data with Qfield to move into a PostGIS database but have been getting this error for some of the features. I have checked the attribute tables and the length of character looks fine to me....
0
votes
2
answers
72
views
How to change coordinate system in PostGIS database: alter, transform or update?
I created an empty database in PostGIS and I need to change the coordinate system from EPSG:4326 to EPSG:32632. I am confused if I should be transforming, altering or updating? Also, how do I know ...
3
votes
2
answers
84
views
Building a WFS whose features hold lists of records
I've got two tables in a PostgreSQL/PostGIS database:
UTM (geom geometry(Polygon,25830),utmcode character varying(6))
Species (species_name character varying(50), utmcode character varying(6), ...
- 2,283
0
votes
1
answer
34
views
Rows with NULL geometry or Null after running pgr_createTopology
After running the code below on a line shapefile that I imported into my postgis database it tells me I have many rows with NULL geometry. I think this might be related to the split with lines ...
- 203
1
vote
1
answer
55
views
Accessing pgr_Dijkstra return values, including aggregated cost
I have the following pgr_Dijkstra SQL code for determining the shortest path between two points on a directed water stream network. The results of the query don't give the total_cost or aggregated ...
- 203
1
vote
2
answers
77
views
Optimizing PostGIS view including counts with attribute join
I try to create a PostGIS view in which I want the number of features having an attribute date < 3 years from current date, and the total number of features, to be able to display a pie chart in ...
- 83
0
votes
1
answer
31
views
Migrating from PostGIS 1.5 to 3.2: need to replace geography_typmod_dims
I can't find a replacement function for geography_typmod_dims which is used in an old application.
In 1.5 there were:
geography_typmod_dims, _in, _out, _srid and _type.
In 3.2 I find only:
...
- 1
0
votes
0
answers
39
views
QGIS processing modeler to PostGIS
I made a processing model in QGIS 3.10 where I calculated a integer value "mycolumn". Within the model, I refactored the "mycolumn" value to Boolean using mycolumn >= 1. As I ...
- 133
0
votes
0
answers
26
views
Point vs multipoints when creating a view it automatically decided to go to multi
I have a table with unique points, when creating a view it automatically decided to convert this geom to multipoints, so to do that it stores a second point with same details just varying the ...
- 226
2
votes
1
answer
63
views
Running osm2pgsql 1.4+ with custom projection, proj 8.2
first some context... I have a custom projection, here is the projection string:
<5514> +proj=krovak +lat_0=49.5 +lon_0=24.83333333333333 +alpha=30.28813972222222 +k=0.9999 +x_0=0 +y_0=0 +ellps=...
- 21
0
votes
0
answers
20
views
Storing movement vectors in DB
I'm not sure if this question is applicable to here or the stats exchange. Maybe some GIS folk have taken a good look at GIS stats?
I've got a postgres+postgis+timescaledb database that has billions ...
- 1,211
2
votes
1
answer
35
views
Intersecting Polygon by a Gridded Polygon returns unexpected result
I have a regular polygon grid (grid) and irregular polygons (block). You can download the data here: https://we.tl/t-BczpR2K2lw
I want to intersect the orange polygons (block) by the regular polygon ...
- 3,017
3
votes
1
answer
103
views
Recursive intersection search to discover paths over a large polygon dataset
Summary
I have a PostgreSQL table of some 80M polygons, and I am attempting to trace paths of connectivity from known starting points within areas of interest. The data is three-dimensional-ish, ...
- 59
3
votes
1
answer
38
views
PostGIS - Parse coordinates of st_isvalidreason() to a geometry column
With PostGIS, I'm using st_isvalidreason() function to check topologicals errors in my entities. When something is topologically wrong, it returns me something like:
Self-intersection[969880.222423769 ...
- 1,325
5
votes
1
answer
100
views
PostGIS azimuth of line segments
I initially drafted a workflow that breaks down a road network and categorizes each line segment as by compass direction such as NS (north-south), EW (east-west), NW (north-west), NE(north-east)
The ...
- 359
3
votes
1
answer
43
views
PostGis - Split polygon into two based in point
How I split this single polygon into two polygons based on the point where there is an intersection?
- 63
3
votes
2
answers
75
views
Create a new table, sorted by st_area function in descending order
I have a very large table in postgis which has more than 10 million polygons and I wanted to create a function using this table for pg_tileserv but the problem is the size of table which takes too ...
- 41
1
vote
1
answer
52
views
Combining multiple PostGIS geometry collections into one
I'm having an issue with combining multiple geometry collections. My sample data consists of 2 records in a trips table, each with a track. There could be many more records, tho.
Here's the first ...
- 163
1
vote
1
answer
59
views
pgRouting: How to start path search based on source and target locations in other layers?
I have a line network and two ESRI point shapefiles, where one is the source locations (from_pts) and the other is the target locations(to_pts). I have done the following to prepare my network, but I ...
- 203
1
vote
1
answer
37
views
psycopg2: ERROR occured!!! rt_raster_to_gdal: Could not load the output GDAL driver
I am trying to query a raster from a database using python. By doing so, I always receive the following exception: ERROR occured!!! rt_raster_to_gdal: Could not load the output GDAL driver. This error ...
- 61
2
votes
1
answer
25
views
Transform PointZ/LinestringZ/PolygonZ's z-coordiante values from feet to meters in PostGIS?
In PostGIS there is the ST_Transform() function that can re-project the XY coordinates of Point/Linestring/Polygon/etc to another coordinates system including meters-based projection to a feet-based ...
- 176
2
votes
1
answer
32
views
Date operation in the Query Builder withPostGIS layer
I would like to filter a PostGIS layer by a datetime (QDateTime) field. I only want to show elements newer then 14 days. I tried the following expression in the Quary Builder:
(day(now() - "...
- 23
3
votes
1
answer
32
views
Unable to edit data in a cloudSQL PostGIS database version 3.10+
I recently created a PostGIS database in an instance of Google cloud SQL.
I can view the data fine and I don't get an error message when I enter editing mode but I am unable to edit any data either ...
- 61
3
votes
0
answers
43
views
Set a spatial index on a set of rows (without table) in PostGIS
I got a giant geometry that I retrieve from an API and I break it into smaller pieces with ST_Subdivide.
Right after, I'm inserting it into a temporary table (which is indexed and only contains a ...
- 71
2
votes
1
answer
60
views
ST_Difference drops M values: How to subtract line segments
In PostGIS, I have a LINESTRING M and I want to remove a segment of the line that falls between two given M values. I tried the following:
SELECT
ST_AsText(line) as line,
ST_AsText(...
- 83
1
vote
1
answer
54
views
pgrouting createTopology error: column source not found!
I tried to create a topology, this is the first steps in pgrouting.
I executed
select pgr_createTopology('public.line_shapefile', 0.00001, 'geom', 'id');
It returned the following error. seems like ...
- 680
3
votes
0
answers
45
views
Splitting edges in PostGIS for network analysis
I'm trying to use PostGIS and pgRouting to do some walking distance analysis, using pgr_drivingdistance. I can get this to run without a problem. However, there are only a limited number of nodes on ...
- 165
2
votes
2
answers
199
views
PostGIS: Is it safe to always call ST_MakeValid to "fix" geography polygons?
I have a GeoJSON file with latlong polygons for the US state of New Mexico. I've loaded them into Postgres, using the geography column type.
For some of the polygons, I get an error when calling ...
- 163
2
votes
1
answer
75
views
Trigger to update foreign table
I have two similar tables (sections) in two different databases (QGIS cloud database and AWS PostgreSQL database). I update the QGIS Cloud database (so that everyone in my organization can see the ...
- 81
1
vote
1
answer
29
views
Adding direction to undirected graph
What I'm trying to do is traverse a known path in a given direction for a given distance.
What I have done so far is build a temp table of the path in the order I want to travel and it looks like:
...
- 313
0
votes
0
answers
55
views
Getting nearest city to point [duplicate]
UPDATE: This question was entered, but by mistake I re-asked in another post, so please consider this post as the one that answer this. This question should be closed and mark as duplicate.
I have a ...
- 159
2
votes
1
answer
79
views
Getting nearest city related to point in PostGIS
I have a table clusters with points.
CREATE TABLE public.clusters (
cluster_id int4 NULL,
punto public.geometry NULL,
cnt int8 NULL
);
INSERT INTO clusters ...
- 159
2
votes
2
answers
77
views
Disperse points with the same coordinates within a certain radius using PostGIS
I had located on my table I have sets of points sharing the same coordinates and they are clustering, is there a way to regenerate their geoms so they get dispersed or redistributed within a certain ...
- 83
4
votes
1
answer
128
views
Incorrect ST_Area after changing to 4326 in PostGIS
I'm trying to change the geometry in our database from 3857 to 4326 so that I can use more functionality within PostGIS. It converts them all properly when I run the alter column and transform query:
...
- 195
2
votes
0
answers
48
views
ST_Rotate in geometry Vs ST_SetRotation in raster
I have one polygon geometry (Square to be precise). I tried to rotate that geometry via ST_Rotate(). Then I stored that same geometry in a raster and tried to rotate the raster with ST_SetRotation().
...
5
votes
4
answers
126
views
Buffer with PostGIS in the form of a pentagon or hexagon
In PostGIS, the ST_Buffer function allows a user to specify the segements per quadrant. However, only polygons with multiples of 4 (4, 8, 12, 16, etc) are allowed.
How would you buffer a point to a ...
- 147
2
votes
1
answer
121
views
Failed to run GetFeatureInfo: precision cannot be greater than 7 or less than -7
I have an environment consisting in Geoserver 2.20 + Postgresql 14 + Postgis 3.1.4, in which I'm trying to run the following GetFeatureInfo request on a database containing geographic information:
...
- 23
1
vote
0
answers
38
views
Import a shapefile in a PostGIS table with a custom geometric column name
PostgreSQL 13.6, PostGIS 3.2, QGIS 3.22
I have a table with several geometric columns:
CREATE TABLE IF NOT EXISTS activite.prescription
(
gid integer NOT NULL DEFAULT nextval('activite....
- 1,045
2
votes
1
answer
810
views
Getting "No function matches the given name and argument types" when converting points to Polygons / Voronoi Diagram
I have these points
stored in my database. Now I am trying to use to create a Voronoi diagram with ST_VoronoiPolygons(). I have tried doing:
SELECT
(ST_DUMP(ST_VoronoiPolygons(ST_Collect(geog)))...
1
vote
1
answer
72
views
Use flex-output and lua style file with osm2pgsql
I want to use osm2pgsql. I have a osm.pbf file for whole Europe from geofabrik.
I am only interested in features (point and polygons) containing the tags
Religious
amenity=place_of_worship (is ...
- 3,017
0
votes
0
answers
41
views
PostGIS Similarity between drawn polygon (x,y between [0,1]) and set of buildings (SRID 4326)
I would like to find the set of buildings (SRID 4326) that are similar to a shape drawn by a user (x,y between 0 to 10)**
Is it possible to do that with PostGIS?
Requirements:
A polygon drawn by a ...
- 153
1
vote
0
answers
267
views
Failing to deserialize geometry point data in Spring Boot REST API
I am working on a location-based REST API and so far everything is working fine, except for one controller which gives this error. I am new to this.
2022-03-16 16:28:37.842 WARN 7720 --- [nio-8080-...
- 11
0
votes
0
answers
71
views
ogr2ogr Multi Surface, Multi Polygon
estoy tratando de migrar una GDB a una base de datos en postgist con OGR2OGR de la siguiente manera
ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user='postgres'
password='...
- 1
2
votes
1
answer
76
views
Proj pipeline support in PostGIS
I would like to use proj pipeline in PostGIS ST_Transform function.
My sample SRS is as follows:
+proj=pipeline +step +inv +proj=unitconvert +xy_in=m +xy_out=us-ft +z_in=m +z_out=us-ft +step +inv +...
- 160
1
vote
2
answers
96
views
Unexpected output from ST_Buffer at large radius
SELECT ST_GeomFromGeoJSON('{"type":"LineString","coordinates":[[-3.01055556,56.46583333],[-71.20583333,46.825]]}')
gives me a line from Dundee to Quebec.
I try to ...
- 113
2
votes
0
answers
40
views
Import a Postgis raster into a QGIS graphical modeler
I'm creating a model using the Graphical Modeler on QGIS (version 3.16.16 - Hannover).
I also have a PostGIS database, with the raster extension installed.
In my model, I use the "Postgres SQL ...
- 29
0
votes
0
answers
28
views
Does ArcMap support PostGIS spatial datatypes with curves (CURVEPOLYGON and MULTISURFACE)?
I have a PostGIS DB with tables with geometries of datatypes CURVEPOLYGON or MULTISURFACE that I need to read (not edit) in ArcMap (in QGIS it just works fine). I'm using ArcMap 10.8/PostgreSQL 14.0/...
2
votes
0
answers
69
views
QGIS starts a query "ST_SummaryStatsAgg()" on PostGIS3.0 for a raster and needs more than 10min to load
I am working on a test-server (RAID6) implementing PostgreSQL/PostGIS with vector and raster support. The aim is a big database sharing different data types including high resolution (0.5m, also 0.1m; ...
- 21
1
vote
0
answers
63
views
Passing table name as an argument in PostGIS function [closed]
How can I pass my table name as an argument of a function?
CREATE OR REPLACE
FUNCTION public.countries_name1(
z integer, x integer , y integer,
name_prefix text default 'B' )
...
- 41
1
vote
0
answers
45
views
Horizontal coordinate adjustment using EPSG 9621 operation method in PostGIS
We have some Cartesian coordinates which needs to be applied with similarity transformation. The transformation operation method describing how to do the same is explained in EPSG 9621 method.
How to ...
- 160