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
2
votes
0answers
35 views

Get Feature id in OpenLayers from GeoServer/PostGIS

With OpenLayers, I'm creating layers like ol.layer.VectorTile var projection_epsg_name = 'EPSG:900913'; var geoserverLayerName = 'teste:map_shape2'; const tileLoadURL= 'http://127.0.0.1:8081/...
5
votes
1answer
168 views

Leaflet + GeoServer WFST - Inserting Polygons to PostGIS database

I have a GeoServer installation on top of a PostGIS database. I am trying to set up a leaflet webmap (using Leaflet WFS-T plugin) so that I can insert, update and delete polygon features. I have ...
-2
votes
1answer
50 views

Clustering large number of locations

I am trying to cluster points inside the USA for which I have the latitude and the longitude. My condition is that, every point within a cluster should be less than 25 miles away from every other ...
0
votes
1answer
52 views

How can I use my NetCDF file in my PostgreSQL database?

I have a NetCDF file which is 25 by 20 grids by latitude and longitude and contains hourly rainfall data at each grid. In total I have 780 hours worth of data. I was trying to use ArcGIS to create a ...
1
vote
0answers
30 views

Splitting a Polygon by a MultiPolygon is unsupported. Both geom columns are MultiPolygon

I'm trying To st_split() on two tables. the output is like this: Splitting a Polygon by a MultiPolygon is unsupported . Although both fields are Multipolygons. Created new columns where the geometry ...
2
votes
1answer
70 views

PostGIS table intersection Query

So I have a table in PostGIS, and each row has a linestring geometry in it. I would like to do a query to return the whole rows of the table that intersect with the linestring along with the position(...
0
votes
1answer
48 views

Making lines from nodes using OpenStreetMap and PostGIS (+ TileMill)

using openstreetmap data (in a postgis database), I would like to highlight all roads with a certain surface tag in tilemill. I can query for the tag, doing SELECT * FROM planet_osm_ways WHERE '...
10
votes
1answer
280 views

QGIS form performance

Edit: tl;dr QGis apparently casts my primary key on postgresql to text when using the identify tool. This causes the form to be slow in startup because it disregards the index on it. A (tested) quick ...
1
vote
1answer
34 views

prevent ogr2ogr from generating new fid attribute field

I am exporting data from PostGIS to gml using python script with ogr2ogr. But the output generates unwanted new field "fid" that contains the following string: "sql_statement.0" , "sql_statement.1", ....
5
votes
1answer
153 views

Clustering points in PostGIS

I am trying to cluster points in PostGIS. I have the points and the corresponding Latitudes and Longitudes which I have converted into point geometries. I want to cluster points in such a way that all ...
2
votes
1answer
26 views

Connecting MapServer to a raster table in PostGIS

I am new in MapServer and I want to visualize some raster tables in MapServer. My mapfile is: MAP CONFIG "MS_ERRORFILE" "error.log" DEBUG 5 NAME "milad" STATUS on SIZE 600 600 EXTENT -8321547 ...
2
votes
1answer
46 views

Batch import of DEM tiles (with shapefiles) into PostGIS creates non continuous contour lines

I want to import a large amount of ASTER DEM data into PostGIS (covering Europe). I use this script: https://wiki.openstreetmap.org/wiki/Contour_relief_maps_using_mapnik#Importing_ASTER_GDEMv2_data ...
1
vote
1answer
37 views

Postgres ST_Distance_Sphere Runtime Improvements

I need to select all entries from a table, that are within a distance from a point. Each entry is a point geometry (I set a gist index on geom). Current runtime is anywhere between 3-15 seconds, ...
0
votes
0answers
56 views

QGIS 3.10 not loading layers from PostGIS

I am trying to load PostGIS layers in QGIS 3.10 but I don't load them, but in version 2.x I do load them normally. What will be happening, I attached an image of the error No se pudieron obtener ...
1
vote
0answers
58 views

Merge connected lines [closed]

I have Road Network which was selected based on some criteria. Below is image representing it. Each beginning and end of Road Segment is marked with cross: Selection led to fragmentation of this ...
0
votes
0answers
37 views

Clarification on GeoServer Function and how it interacts with databases

I am currently working on a web application which uses OpenLayers to display PostGIS layers served through GeoServer. If I want to create a "user login" type database with username and password ...
1
vote
2answers
63 views

Creating or updating geometry polygon column with new point using PostGIS

I have PostgreSQL tables with the following columns: Table (1) | Column | Datatype | | ------------- |:-----------------------:| | group | TEXT | | ...
2
votes
1answer
67 views

How to get the intermediate points between a route origin and destination?

I use Redshift and recently Amazon Redshift has updated the database to be able to use some spatial functions. https://docs.aws.amazon.com/redshift/latest/dg/geospatial-functions.html In a table I ...
1
vote
1answer
42 views

Handling PostgreSQL IntegrityError from PyQGIS

I am developing a QGIS plugin, in which I want the user to be able to add/delete features from some layers. Here is the code I'm using to delete a feature with id = trayId try: self.traysLayer....
2
votes
1answer
37 views

Trying ST_Slope with SRTM rasters and a shapefile but the output raster has different SRID

I have two SRTM rasters https://dds.cr.usgs.gov/srtm/version2_1/SRTM1/Region_06/ (N40W074 and N40W075) with Datum "WGS_1984" I've united them to be able to work with one raster: CREATE TABLE rasters....
2
votes
1answer
47 views

Splitting self-overlapping lines with points using PostGIS

I have a line geometry that partially overlaps itself (think about a line going A -> B -> C -> B -> D) and a set of points (not on the line but close to it) that I want to use to split the line in ...
1
vote
1answer
41 views

Compare two set of linestrings

I have two sets of linestrings which have to perfectly overlap. The set A have to contains the set B (the A is a combination of multiple Bs which are touching each other). I would like to build a ...
2
votes
1answer
77 views

Finding differences between polygons in PostGIS

I have another redistricting question. The datasets and queries available on my GitHub, but I will include the queries here, too. I am working on two complete datasets: 1) A PLAN - composed of ...
1
vote
0answers
21 views

ogr2ogr gdb to postgis — empty geometry after import

I am using the below command: ogr2ogr -f "PostgreSQL" -s_srs EPSG:2286 -t_srs EPSG:2286 PG:"host=${PGHOST} dbname=${PGDATABASE} user=${PGUSER}" import_file.gdb -overwrite -lco SCHEMA=some_schema -...
1
vote
1answer
34 views

QGIS topology checker: must not overlap

I use the topology checker in QGIS 3 with the rule "must not overlap" for a single PostGIS multipolygon layer. Can somebody explain the meaning of errors (colored in red) when the plugin colors a ...
1
vote
0answers
35 views

PostGIS add polygon in meters around a point (lat,long)

I have a point in my PostGIS database that is put in using the lat/long using srid: 4326. The point represents a car driving, now I want to add a polygon of the cars shape around this point and update....
0
votes
1answer
41 views

Iterate through table to do geometric calculation

I've got two tables: tableA holds the objects with it's geometry tableB holds holds pairs of two objects (ids, each in a separate column) from table1 Now I want to calculate the concaveHull for ...
1
vote
1answer
39 views

Updating certain values in a MATERIALIZED VIEW [closed]

I have created a materialized view and now want to replace a certain value in one of the columns. How can I do that? Perhaps I should use INSERT INTO, but I do not really know how to use this. The ...
2
votes
1answer
40 views

Features of PostGIS layer are only selectable in attribute table but non on map canvas

My features of a PostGIS layer are displayed in the map canvas in QGIS and projected properly at the right place. They are not selectable in graphically in the map. But they are selectable in ...
1
vote
1answer
43 views

GeoServer PostGIS layer with mixed SRS possible?

I'd like to setup a layer in GeoServer based on a table in PostGIS DB. The geometry column in the table has no SRID set (Find_SRID for this table returns 0). Geometries inserted into the table don't ...
4
votes
1answer
95 views

Using `CAST` and `ST_UNION` in same query in PostgreSQL

I want to union the geometry of waterlines grouped by a table column in a new view. My problem is that I apparently needes to specify the geometry type using CAST function. But I cannot figure how to ...
0
votes
1answer
29 views

Merging three linestring tables into one new table view [closed]

I have three tables in a PostGIS database and I want to create a table view merging the features from the three tables into one, by using PostgreSQL. The three tables are containing three different ...
1
vote
1answer
62 views

Setting up QGIS Server/Lizmap with PostGIS database

I've installed Lizmap and set up a local PostgreSQL database for Lizmap configurations. Now I'm trying to run a new QGIS Project with the layers saved in a PostGIS/PostgreSQL database (also local) but ...
1
vote
1answer
36 views

Error when creating PostGIS extension in pgAdmin4

I am trying to create a PostGIS extension in pgAdmin4. When typing CREATE EXTENSION postgis;, or through "db_name > Create > Extension > General > Name = postgis", I get this error: ERROR: could ...
1
vote
0answers
35 views

Implicit geometry typecast via trigger

Because OGC SFS is relatively strict when it comes to mixing Geometries with their MULTI- Counterparts, we came up with a solution to store everything as MULTI... and convert incoming geometries via ...
0
votes
1answer
71 views

How to limit a database layer in QGIS to the extent of another layer's feature?

I am developing a QGIS project template for a client that uses multiple PostGIS layers for viewing/editing structures that are identified from aerial images. In each new project derived from that ...
1
vote
0answers
51 views

QGIS cannot see PostGIS empty tables

QGIS didn't load empty tables with geometry columns from Postgresql, showing this error message: "invalid PostgreSQL layer", but when these tables have data it shows it without any problem. Is it ...
1
vote
0answers
40 views

Numeric separators in GIS [closed]

In Spanish and Catalan, we use numeric separators the other way round: We use a comma instead of the decimal point; and a point instead of a comma to mark the thousands. English | Spanish 10.5 ...
1
vote
1answer
57 views

How to import raster from PostGIS into Python as dataframe

I am trying to import the raster from PostGIS into a raster for further analysis. I know that I should use psycopg2 to connect to DB, but I do not know how to query the raster from DB, and then ...
2
votes
2answers
133 views

Determining if coordinates from feature are in EPSG:4326

My idea is before upload a GeoJSON file, check if geometries are in EPSG:4326. I've tried using PostGIS with Find_SRID but return 0 because when I insert geometry I don't know which SRID has the ...
0
votes
1answer
46 views

How to explode geometry in PostGIS?

I have got two geometries one is polygons (postcode boundaries) and second is linestring (roads). I want to intersect them together and want roads that are intersecting the polygons. While doing this ...
1
vote
1answer
31 views

QGIS: Prevent the Primary Key from incrementing when feature won’t be saved

So, I have a PostgreSQL/PostGIS DB attached to my QGIS project (MacOS, 3.10.). I start adding a feature and cancel the process w/o saving the form. When adding another feature my primary key ...
3
votes
2answers
106 views

Finding holes in proposed districts (Polygons/Multipolygons)

Thinking through something that I seek some SQL on: One of the ideas in redistricting is that a proposed district boundary should have no holes in it, i.e., a chunk of land with no district_id ...
0
votes
0answers
35 views

Importing shapefile into PostgreSQL failing [duplicate]

I have installed PostgreSQL 12 on my Windows 10 OS. I installed the PostGIS extension as well. I would like to import a shapefile and am using shp2pgsql-gui to do so. The import is failing and there ...
1
vote
1answer
45 views

US Census block boundaries shapefile [closed]

I am searching for a single file of all roughly 11 million US census blocks. Where can I find this?
3
votes
1answer
64 views

GeoPandas to PostGIS database

New to using GeoPandas instead of Pandas. I am trying to export a GeoPandas dataframe to a PostgreSQL database (with PostGIS extension). The import is from the same database as I try to export to ...
1
vote
1answer
32 views

PostGIS - “parse error - invalid geometry” after using ST_Multi - but ST_IsValid returns true

I have a database with OpenStreetMap polygons, points and relations in separate tables. Now I wanted to build selection queries for the different features and created schemas for each zoom level (5k, ...
1
vote
0answers
36 views

Query example in pgrouting by PostGIS

I found a query in the web which create catchments by pgrouting. I tried the query several times and modified a bit. But it dosen´t work. It alwys shows me the same error. DO $$ DECLARE c ...
1
vote
1answer
38 views

Accessing PostGIS geometry with psycopg2

I've a PostGIS database loaded with several tables filled with shapefiles. I'm using psycopg2 to access everything via python. I'm able to access the table names with the below query: """SELECT ...
2
votes
1answer
38 views

Sorting polygons into a n x n spatial array

I have a square group of contiguous polygons: By using the centroid coordinates of each polygon, I want to assign each polygon's spatial sequence in a row by row fashion: I have tried this ...