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
3 votes
2 answers
103 views

Why the geometries in 32632 projection aren't perfect?

I am implementing a Tile Server with the help of PostGIS. Running this query: SELECT ST_AsMVT(tile) FROM ( SELECT __FEATURES__, ...
Kambei's user avatar
  • 170
2 votes
1 answer
52 views

How to call newly made variable

I want to calculate the difference between the total count of elements in the "nara_current_wvl" layer and the "same_points" variable created here, and than the difference between ...
AronBrownie's user avatar
4 votes
0 answers
77 views

ST_Contour doesn't produce polygon

With this version: PostgreSQL 14.0, compiled by Visual C++ build 1914, 64-bit POSTGIS="3.3.2 3.3.2" [EXTENSION] PGSQL="140" GEOS="3.11.1-CAPI-1.17.1" PROJ="7.2.1&...
xyz绿洲's user avatar
4 votes
0 answers
83 views

How to handle NULL Z/M values in Postgis?

Using ST_SetPoint on a line with a NaN Z fails select st_setpoint( st_makeline(ARRAY[ST_MakePoint(1,2,'Nan'), ST_MakePoint(3,4,10), ST_MakePoint(5,6, 20)]), 2, ST_MakePoint(5,6, 'Nan')) Fails with ...
Denis Rouzaud's user avatar
2 votes
1 answer
102 views

Determine area enclosed by multiple segments

I am trying to determine the area enclosed by multiple linestrings. For example, in the following picture, I have 4 linestrings, forming 2 closed polygons. I'd like to be able to get the coordinates ...
arno's user avatar
  • 43
3 votes
1 answer
80 views

PostGIS fields of type interval not part of QGIS' field list

I have calculated a flight trajectory dataset from open data which has a couple of fields that can be used in GIS (3.32) without any troubles. but there seems to be a problem with fields of type "...
robert tuw's user avatar
2 votes
1 answer
97 views

Trying to get accurate results using ST_DWithin with srid -1

I have a table where each row has a point Geometry(POINT(...)) [long lat]. These geometries don't specify an srid. Which means we're using a cartesian plane to do the calculations. All of this data is ...
Bombadillio's user avatar
3 votes
1 answer
173 views

Get convex hull of polygons if they are within a certain distance of each other otherwise leave their geometries alone

What the question says, I want to join geometries together (as a convex hull) if they are within a certain distance of each other, and leave them as separate geometries if they have no neighbors ...
rumski20's user avatar
  • 588
2 votes
1 answer
52 views

PostGIS ST_Area() accuracy for huge polygons [duplicate]

I am trying to understand the behavior of ST_Area() in these examples. I'm using PostGIS 3.2. In both cases, the polygons are horizontal bands spanning half the globe from the antimeridian to the ...
Matt Skone's user avatar
0 votes
1 answer
177 views

Import GeoPackage(GPKG) to PostGIS with styles

I've tried to import GPKG file data into PostGIS using ogr2ogr, but can't seem to understand if it is possible to import the style symbology contained within the GPKG aswell. I've noticed ogr2ogr adds ...
Geospatial Data Enjoyer's user avatar
6 votes
2 answers
121 views

Is there a common method for measuring "curviness" of a line?

I have a bunch of GPS tracks in a Postgres+PostGIS database and want to extract a single metric as a measure of turns shown in the data. Some of the GPS targets travel in almost straight lines while ...
RedM's user avatar
  • 1,515
0 votes
0 answers
39 views

Accident-Prone Roads: Segmentation Challenge

I am trying to analyze how many crashes happen relative to the amount of traffic on roads in California, to which end I have the following data (from Caltrans, California's DOT) Roads (LINESTRING and ...
Lars Skaug's user avatar
3 votes
1 answer
106 views

Set coordinates as default value

In PostGIS, how to set default value with st_x or st_y ? I want to populate automatically two fields with lon and lat when I create a point.
pasqal's user avatar
  • 508
2 votes
0 answers
47 views

Data deleted from PostGIS database while working and editing the spatial data

I have large amount of data and around 40 users working on it and doing editing in roads, rivers etc and updating attributes. I am using QGIS 3.26 for this. Today four main and large layer data ...
Paul P's user avatar
  • 113
3 votes
2 answers
113 views

How to Append a POINT to a MULTIPOINT column

I have a table in postgresql that includes a geometry(MultiPoint,4326) column. I want to run a process that appends a new MULTIPOINT(4.49306 52.26167) to this column. I am currently doing this: UPDATE ...
bcsta's user avatar
  • 191
3 votes
0 answers
111 views

Speed up a query using ST_Within and ST_PointOnSurface

CREATE TABLE municipal_area ( id varchar NOT NULL, "name" varchar NOT NULL, state_id varchar NOT NULL, state_abbr varchar NOT NULL, state_name varchar NOT NULL, ...
jcollum's user avatar
  • 153
0 votes
0 answers
53 views

ogr2ogr PostgreSQL driver doesn't seem to work with list of schemas

I have a PostGIS database with a table vector_data in a non-default schema. I use blue/green deployments for this database, so sometimes that table is in the vectors_blue schema, and sometimes it's ...
Matt Skone's user avatar
2 votes
1 answer
74 views

Why doesn't ogr2ogr support the PostgreSQL schema search path?

I have a PostGIS database with a table vector_data in schema vectors, and the vectors schema is listed in the database search_path. In GDAL <= 3.0.4, this command works fine (that is, the ...
Matt Skone's user avatar
4 votes
3 answers
70 views

Intersecting Node A and Node B columns for lines table

In PostgreSQL, I have one view (LineString) and one table(Point). Some line features intersect with some points at the beginning and end nodes. I'd like to create another view and have columns named &...
foxhq's user avatar
  • 141
0 votes
0 answers
40 views

How to get the 3D intersection between a Line and a raster in PostGIS?

I successfully loaded a Geotiff .tif file into PostGIS using raster2pgsql, split into 20x20 tiles with the following command: raster2pgsql -s {srid} -I -C -M -t 20x20 {tif_file_path} {table_name} > ...
alexsaleh's user avatar
0 votes
1 answer
179 views

How to parse latitude, longitude from geography(POINT, 4326) using Psycopg in Python

I am using PostGIS to save geography data in type geography(POINT, 4326). CREATE TABLE IF NOT EXISTS address ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), geog geography(POINT, 4326) NULL ) ...
timewalker's user avatar
5 votes
2 answers
97 views

ST_Polygonize creating single polygon

I have a situation that is very similar to the issue described here. However, the accepted answer does not work for me. I am essentially attempting to create polygons from contour lines generated ...
rumski20's user avatar
  • 588
1 vote
1 answer
35 views

ogr2ogr only imports 99999 rows of geopackage layer to PostGIS database

I am using the ogr2ogr command to upload a geopackage layer to my PostGIS database. The layer has 130'870 objects/rows. I am using this code: ogr2ogr --debug ON -progress -f "PostgreSQL" PG:&...
theo_maptransfer's user avatar
5 votes
1 answer
134 views

Creating LineString from points by group of time period using SQL

I have a serie of GPS points with dates and a state ('flying' or 'grown') from different individuals. I would like to get lines from those points by individual, grouped by state BUT still ordered by ...
Remi79's user avatar
  • 163
2 votes
0 answers
60 views

PostgreSQL 'UNION ALL' statement in a view does not load in QGIS

I have two datasets, sharing the same attributes (but being distinct from each other), each in their PostGIS table. The tables are huge (both geometrical and containing ~1 billion features) and have ...
Laurens's user avatar
  • 71
3 votes
1 answer
72 views

Importing Overpass API response to PostGIS with ogr2ogr creates empty table

I need to import parking polygons from OSM to PostGIS. But when I import data from Overpass API using ogr2ogr it keeps creating an empty table (with the expected columns as to per osmconf.ini). Import ...
Christa Rohrbach's user avatar
2 votes
2 answers
191 views

Creating vector grid in QGIS for two different polygons without overlapping

In QGIS I have the data for all the continents except Antarctica. Now I want to create a grid of size 1km * 1km for each country however the problem I am running into is that if I take two ...
Arup Chakravorty's user avatar
2 votes
1 answer
104 views

st_generatepoint returns invalid memory alloc request size 4305734400

As a result of some analysis I am left with this polygon (there are probably others, but this is one example): SELECT st_astext(geom,1) FROM flood.temp WHERE id = 9695 POLYGON((29.8 52.2,29.8 52....
TheRealJimShady's user avatar
1 vote
0 answers
29 views

Error when Saving PostgreSQL Cloned Table

I'm facing an issue while working with a cloned table from a PostgreSQL schema. When attempting to make changes like adding new vector layers or deleting a layer and then saving the project (from QGIS)...
user231934's user avatar
2 votes
1 answer
51 views

Separating mixture of two different coordinate systems in PostGIS table

One PostGIS table includes features (point, lines, polygons) with two different coordinate systems: EPSG:27700 (BGN) and EPSG:4325 (WGS84). Spatial extent of the table in WGS84: 52.6967,-3.1401 50....
foxhq's user avatar
  • 141
4 votes
0 answers
81 views

Spatial database design [closed]

I am designing a spatial database and I wanted to ask if it is a good idea to use inheritance to store objects that share certain attributes, but whose geometry differs since some are points, lines ...
Javier Aguilar's user avatar
1 vote
1 answer
86 views

Latitude/longitude from PostGIS table with coordinates transformation not working

I have a PostGIS (PostgreSQL 13/PostGIS 3.1.1 on Debian 11.7) table with points in EPSG:6708, so it's plain coordinates (data from Northern Italy). I need to get lat/long coordinates, so I do: ...
Luca Pauluzzi's user avatar
1 vote
0 answers
42 views

GeoServer layers data not showing on hosted WebGIS [closed]

I made a webgis and it has feature adding layer from GeoServer. It works fine on localhost but when I hosted the website its only hosting the HTML and cannot get/show the data from the GeoServer. Is ...
Maulana Heno's user avatar
1 vote
1 answer
82 views

Formation of MVT on the basis of GeometryCollection

The PostGIS documentation for the ST_AsMVT method states that ST_AsMVTGeom can accept a GeometryCollection for the initial formation of an MVT object. I execute ST_AsMVTGeom with ST_Collect combining ...
Roman's user avatar
  • 53
4 votes
1 answer
169 views

How SRID is represented as part of EWKB [closed]

I am trying to find the spec that defines how the SRID metadata is included as part of EWKB in PostGIS. Which bytes/hex values represent the SRID metadata in EWKB? Where can I find a spec on EWKB ...
Riyafa Abdul Hameed's user avatar
2 votes
1 answer
133 views

How do I share my data to others with PostGIS/QGIS

I only started to learn using PostgreSQL with QGIS. Since I wouldn't want my local machine to be actively running PostgreSQL in the background for always-on access, is there a way to "share" ...
Cookie's user avatar
  • 355
2 votes
1 answer
77 views

Difference between PostGIS pgrouting and Lizmap pgrouting

I'm starting to work with pgrouting. I know that pgrouting is an extension of PostGIS. I saw that Lizmap (which I currently work on) provides a module called pgrouting. Two points are drawn from the ...
Guido Fonzo's user avatar
1 vote
0 answers
71 views

Updating existing PostgreSQL database with OSM data

I currently work on the project which is directly working with the geo data of the country where I live. The database we have is quite huge, containing not only the geo data but also some other stuff. ...
qwerty's user avatar
  • 11
0 votes
0 answers
80 views

Uploading large OSM files to PostGIS with command line

I'm trying to upload the nationwide US OSM dataset from Geofabrik website to a PostGIS database (about 200 GB). For small OSM files, I'm able to import the OSM file as layers into QGIS with the ...
Vivek Reddy's user avatar
2 votes
1 answer
77 views

pgr_withPointsCost returns incorrect table values

I have been struggling with getting pgr_withPointsCost to output a correct cost table for months. I have created a routable network, ensured the network is connected, created a poi table with all the ...
MJM's user avatar
  • 975
1 vote
0 answers
50 views

Visualize a linestring and point from a PostGIS database in QGIS?

I ran a query in PostGIS table using a table of roads/linestrings in Hawaii and another table with 400,000 (longitude,latitude) points in Hawaii. The output table is meant to provide nearest road in ...
Vivek Reddy's user avatar
2 votes
1 answer
67 views

How to use ST_Union if polygons share the same name using PostGIS SQL

Say I have a polygons that overlaps with each other, How do I achieve the same output as dissolve -> dissolve field(check "name") using SQL code? layer fields information: - name # <-- ...
Cookie's user avatar
  • 355
2 votes
2 answers
94 views

How to prevent duplicates from being generated in Spatial Join in PostGIS

I'm trying to create a spatial view which meets the following criteria: Sentinel hotspots registered within 2km of powerline (feeder) AND QRFS point located within 4km radius of sentinel hotspots AND ...
Fezter's user avatar
  • 21.9k
0 votes
0 answers
39 views

Store remote sensing metadata created from STAC into PostgreSQL/PostGIS

I have created some STAC catalogs for remote sensing data (Landsat, etc.) that I would like to store in PostgreSQL/PostGIS for easier access and management. How would I import the STAC created ...
Clouseau's user avatar
0 votes
0 answers
50 views

Does ST_ClusterKMeans benefit from a spatial index?

Does ST_ClusterKMeans benefit from a spatial index? here is how I am using it (24313 is projection in meters): ST_ClusterKMeans( ST_Transform(d.bf_polygon, 24313), ...
analyst92's user avatar
  • 837
1 vote
1 answer
169 views

How to check if a raster in PostgreSQL table is tiled

I have inherited a PostgreSQL db and I am trying to run queries like this: UPDATE geomtable a SET column = ST_Value(b.rast, 1, a.geom) FROM rastertable b WHERE ST_Intersects(b.rast,a....
Goomer's user avatar
  • 13
2 votes
1 answer
107 views

Query to find nearest road highway tag to a (latitude,longitude) point

I imported an OSM file for the state of Delaware into the PostGIS Database via connecting to it through QGIS. I would like to build a query to retrieve the nearest roads to the (latitude,longitude) ...
Vivek Reddy's user avatar
2 votes
0 answers
138 views

PostGIS error during a simple SELECT statement: Geometry SRID (2056) does not match column SRID (21781)

I am facing a strange error in a PostgreSQL 13.6 + PostGIS 3.2.0 database on a Window 10 machine: mydb=# SELECT * FROM my_schema.my_view; ERROR: Geometry SRID (2056) does not match column SRID (21781)...
swiss_knight's user avatar
  • 10.3k
1 vote
0 answers
46 views

PostGIS selecting points using a subset of lines using ST_DWithin [closed]

I'm not sure why this script sends a much higher count than the real number. This one sends 2314 points, which the answer should be 114 I can get the correct value by either dissolving all the ...
sMartines's user avatar
  • 121
1 vote
1 answer
60 views

Minimum spanning tree (MST) over a set of POINT geometries

I have 3 columns in my table: centerPoint (point), uuid (string), and "areaID". Each areaID can have multiple uuid's, and each uuid has a corresponding centerPoint. I want to calculate the ...
analyst92's user avatar
  • 837