A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

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
1
vote
1answer
20 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
33 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
50 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
26 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 ...
0
votes
0answers
41 views

Converting 10m .tif file to 250m pixel using PostgreSQL functions and store it as PNG [closed]

I have to convert a downloaded 10m pixel .tif file to 250m pixel using postgresql functions and store it as PNG in PostgreSQL database. I'm getting 10m pixel file which I have to convert to 250m and ...
0
votes
2answers
52 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
49 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
41 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
31 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
41 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
34 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
61 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 ...
0
votes
0answers
46 views

Should I use PostGIS? [closed]

I am new to PostGIS, so I am not sure if I actually need to use it or not. I have several shape files and csv files with information that has latitude and longitude. I will be doing a lot of queries ...
1
vote
0answers
18 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
30 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 ...
0
votes
0answers
32 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
36 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
36 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 ...
-1
votes
0answers
16 views

Having problems with SQL View using DVF [closed]

I'm working on a project as student and my goal is to create a web api for a real estate agency. Im using a database called DVF provided by the country (FRANCE). I'm new to geoserver and its been ...
1
vote
0answers
33 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
40 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
91 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
46 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
31 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
34 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 ...
1
vote
0answers
43 views

Using area or $area function in QGIS, when the data (polygons) is stored in UTM32N (25832)?

I have a question regarding the area and $area function in QGIS and St_Area in Postgis. I am trying to calculate the exact area of Polygons stored in a Shapefile in UTM 25832 and do not know which ...
0
votes
1answer
65 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 ...
0
votes
0answers
36 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
38 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 ...
-2
votes
0answers
17 views

Tiger, Tiger_data and Topolgy schema What data they storage? [duplicate]

I am new in PostGIS. When I install PostGIS, the "demo" database creates four schemas, Public, Tiger, tiger_data and Topolgy. which one is the information or data that Tiger, tiger_data and Topology ...
1
vote
1answer
47 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
128 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
42 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 ...
2
votes
2answers
84 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
44 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
47 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
26 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, ...
0
votes
0answers
30 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
30 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
37 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 ...
1
vote
2answers
82 views

Layer from PostGIS view with dissolve slower than without in GeoServer

I created a view of a table containing line segments, where many lines may be part of the same project (and not necessarily adjacent) in PostgresSQL/PostGIS. The segments are joined to another table ...
1
vote
1answer
50 views

Using views with 1:N relation QGIS

Working with PostgreSQL basic view in QGIS I noticed that: Select * from t1 where t1.column = 'some_condition' Views doesn't support constraints according to this topic: https://stackoverflow.com/...
0
votes
1answer
27 views

Select evenly distirbuted points of unevenly distributed set

I have a task which looks pretty nontrivial yet pretty standard. Let's say, I have a set of points, unevenly distributed on the map (i.e. in some regions there are a lot of points, while in the ...
1
vote
1answer
45 views

Creating linear event table from geometry using QGIS Linear Referencing

I am trying to find a way to dynamically create an event table from a linear feature. Imagine the example below. Registration of a sidewalk/fence or similar. The colours represent different routes. ...
1
vote
1answer
35 views

Select points within a buffer around a line

Working with PostgreSQL. I have a Table with streets (Linestrings), and a Table with accidents (Points). The streets are divided into sections. I want to find the accidents within 50m around a certain ...
0
votes
0answers
17 views

Are there annual or monthly NDVI high res datasets? [duplicate]

I'm looking for a source of high res (250 meter seems the highest res available for MODIS?) NDVI composite datasets for either an entire year (or monthly snapshots) if not, in a format I can import as ...
3
votes
0answers
53 views

Which one must be the result of st_difference of polygons? st_touches true or st_intersects true?

I've two tables of polygons, one has 1282 polygons, other has one big polygon. I use st_difference to have polygons that are not part of the bigger one. After st_difference i tried st_touches on ...