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
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....
user avatar
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 ...
user avatar
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), ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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: ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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=...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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, ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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?
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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() - "...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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(...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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: ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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: ...
user avatar
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(). ...
user avatar
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 ...
user avatar
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: ...
user avatar
  • 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....
user avatar
  • 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)))...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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-...
user avatar
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='...
user avatar
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 +...
user avatar
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 ...
user avatar
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 ...
user avatar
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/...
user avatar
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; ...
user avatar
  • 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' ) ...
user avatar
  • 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 ...
user avatar