Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
7,257
questions
1
vote
2answers
85 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
52 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
31 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
50 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
37 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
20 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
57 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 ...
2
votes
0answers
44 views
Split a linestring by distance every x meters using PostGIS
I'm looking to split a linestring every x meters. I don't know which way to start, and I'm going in circles! I can find a point at 20 % from the start with ST_LineInterpolatePoint, but not at 20 m. ...
1
vote
1answer
39 views
PostGIS shapefile import manager error: syntax error
I'm trying to use PostGIS Shapefile Import/Export Manager to import a shapefile that was created in QGIS. The shapefile has all the blocks of the 2010 U.S. Census for Oregon and was exported as a ...
1
vote
1answer
33 views
Length of a street-Spatial SQL
I have a table with the geometry of streets and I`d like to find out, which is the longest and which length (in kilometers) it is.
How can I summarize the individual street segments?
1
vote
2answers
43 views
PostGIS calculate distance to every point in table
I would like to calculate distance from point made by putting coordinate manually to every point in table. I made that code:
SELECT a.name
FROM ne_110m_populated_places as a
CROSS JOIN (SELECT ...
2
votes
1answer
57 views
Perform intersection using a TRIGGER
I've two geometry table inside my DB:
buffer: id INTEGER SERIAL PRIMARY KEY, geom POLYGON
polygons: geom MULTIPOLYGON
I need have the intersection between buffer and polygons and I will do this ...
1
vote
1answer
48 views
QGIS DBManager: Error Adding Layer
Background: I am running QGIS 3.10.1, PostgreSQL 12.1 and PostGIS 3.0.0 on Windows 10. Using this environment, I am trying to run a simple query in DB Manager:
SELECT polygon('((10,20),(25,30),(30, ...
1
vote
1answer
26 views
Converting polygons to/from raster coordinates with PostGIS
I'm looking to convert polygons from one set of imagery to a another (imageryA -> world -> imageryB) and I'm struggling to come up with an in-database solution. I have a solution in python, but we ...
1
vote
1answer
42 views
Mimicking ArcGIS Combine function in PostGIS
I am trying to use PostGIS to create something similar to the attribute table produced when two raster files are combined using the ArcGIS Spatial Analyst function "Combine". This shows the count of ...
0
votes
0answers
31 views
Extremely slow Nominatim install with Docker
I am trying to install Nominatim based off of a dockerized version of the tool found here: https://github.com/mediagis/nominatim-docker. I was successful with installing dockerised Nominatim with a ...
3
votes
2answers
80 views
Aggregating numerous polygons into one with PostGIS
I have this PostGIS query which aggregates many polygons according to a variable ("region"):
SELECT ST_AsGeoJSON((ST_Collect(polygons)),3) as geom FROM table GROUP BY region;
The ST_Collect returns ...
1
vote
1answer
17 views
ST_Collect while computing average values
In a PostGRES/PostGIS database, I have a table "myTable" containing very large numbers of polygons (the geometry is under the column "Polygon"). Each polygon has a numerical value under the "Var1" ...
3
votes
1answer
63 views
QField App crashes when connect to internal server of PostgreSQL and PostGIS
I'm completely new to GIS field and here what I'm trying to do:
I want to make the QField App connects to internal server which has a PostgreSQL and PostGIS running on it.
I created a schema in ...
3
votes
2answers
135 views
How can I extend a linestring to the edge of an enclosing polygon in PostGIS?
I have linestrings that are each entirely within a polygon. I want to extend the first and last segments to touch the first rings of the polygon in line with them.
0
votes
0answers
20 views
Create PostgreSQL table from GeoJSON data FeatureCollection [duplicate]
I would like to store GeoJSON data into PostgreSQL database.
WITH data AS (SELECT '{ "type": "FeatureCollection",
"features": [
{ "type": "Feature",
"geometry": {"type": "Point", "...
2
votes
2answers
84 views
GeoServer parametric SQL view. ERROR: function key(boolean) does not exist
I have been had a task: to allow a single WFS request to return two different responses depending on zoom: return usual
SELECT * FROM table
if zoom level >n, and return
SELECT ...
1
vote
0answers
30 views
Why Server Vector Tiles for MapboxGL Client rendered incorrectly
I am trying to set up a web server in Node.js that serves vector tiles to be displayed in a browser using MapboxGL JS. The data for the vector tiles is stored in a PostGIS database.
I receive a ...
1
vote
0answers
41 views
GeoDjango query raster pixel values in PostGIS
I am building a REST API in GeoDjango that queries pixel values on a multiband raster held in PostGIS (out of db).
I have defined the following model to match the raster held on the database:
from ...
0
votes
1answer
33 views
Query different PostGIS tables in the same QGIS expression
I connected QGIS to my local PostGIS database, which contain several tables. It looks like this:
I dragged and dropped one of the tables to the Layers area and started visualizing one of the columns ...
2
votes
1answer
56 views
Get location of PostGIS / GEOS topology exception
I use ST_LineMerge to combine many overlapping lines, splitting them where they branch off. A prerequisite to this process is to ST_Collect my geoms of interest, then ST_Node them to ensure all ...
0
votes
0answers
11 views
How to Update ImageMosaicJBDC Store in GeoServer
Background:
So I recently posed another question here, asking how one might combine elevation data and serve it over WCS. Without going into too much detail, one suggestion I got was to create and ...
1
vote
2answers
56 views
How to use SQL view request for WFS from GeoServer: Apply PostGIS function depending of zoom
I have a task
to allow a single WFS request to return two different responses depending on zoom:
return usual SELECT * FROM table if zoom level >n and
return SELECT ST_SimplifyPreserveTopology(geom, ...
-1
votes
2answers
37 views
Creating a line in PostGIS [closed]
I have a table with 2 pairs of coordinates and I would like to create a line that connects these coordinates to show their connection.
x1, y1 must connect to x2,y2.
I have read about St_MakeLine ...
3
votes
0answers
36 views
Reconciling RGeo spheroid calculations with PostGIS
I have a Rails/Postgresql app that uses a combination of RGeo and PostGIS, and I'd like to move more calculations in to RGeo and reduce some of the load on the database. One of the problems I'm having ...
0
votes
2answers
45 views
How to get derived data in QGIS or R?
I am struggling to get derived data from a shapefile. In the attribute table of the shapefile, I can see just the id, initialdat and finaldate.
Does anyone have a good solution to get the derived ...
2
votes
1answer
227 views
ST_Intersects vs ST_Within
Assuming that I have a point on a map and a multipolygon on a map. When I query with ST_Intersects or ST_Within I get the same result. I want to know what is different between these two methods?
1
vote
0answers
78 views
Creating a dissolved buffer layer in PostgreSQL/PostGIS
I have got a MultiPolygons layer to which I want to create 10 meter buffer around and then dissolve. All data are having SRID 4326.
I am new to PostgreSQL/PostGIS. I am drifting around the ST_Buffer ...
0
votes
0answers
39 views
RAISE NOTICE from trigger to QGIS
I work on vue postgresql made with table,
so for save I have to use trigger.
My trigger do a lot of thing and I have to send message to the user to make him understand what happend.
But QGIS ...
2
votes
2answers
56 views
ST_MakeLine based on a field or equivalent
I want to create a set of lines taking into account the value of an attribute. As soon as the value of this attribute changes, then I start a new line. Currently, I use the FME LineBuilder tool where ...
1
vote
0answers
27 views
Restrict editing access to my PostgreSQL/ PostGIS database used through QGIS [closed]
We have a small network of users accessing our QGIS data stored on a PostGIS database which sits on a 'Q' drive in my computer. This drive is then shared with the others.
Due to the other users being ...
2
votes
1answer
100 views
OSM + PostgreSQL Queries
I'm newbie to PostGIS and am looking for some SQL examples for better understanding.
I didn't understand how do I manipulate the tables, how objects relate one to another. An example:
There is this ...
0
votes
1answer
36 views
Saving ST_Envelope result gives error: Geometry type (MultiPolygon) does not match column type (Polygon)
I'm trying to save the bounding box of a multipolygon into a polygon column like this:
UPDATE "mytbl" SET bb = ST_SetSRID(ST_Envelope(mp::geometry),4326) WHERE "mytbl"."id" = 1
But I receive the ...
3
votes
0answers
46 views
Error trying to split a Linestring in PostGis
I'm trying to split a group of Linestrings when they cross a Polygon shape, I'm using the following query:
SELECT
v.*,
c.name,
(st_dump(st_split(v.geom, c.geom))).geom AS geom
INTO table
FROM
...
2
votes
0answers
83 views
Holes after ST_Split with PostGIS
I want to split polygons with multilines, and merge results with the neighboring polygon.
I tried with this code :
--Polygon splitting
CREATE TABLE decoupe AS
SELECT rect.affectatio, (
ST_Dump(
...
1
vote
1answer
135 views
How do I check if the PostGIS extension to a PostgreSQL data base is working in DBeaver?
I am starting with PostgreSQL and trying out a few things in a small testing DB-server. I have connected this DB DBeaver 6.2.2 and added a few tables and columns. Now I wanted to get some spatial data ...
0
votes
1answer
84 views
Are DBeaver/PostgreSQL Datatype = Polygon real PostGIS spatial Objects?
I am currently designing a database for my PhD-Project. I want to link a PostgreSQL database to QGIS. I just started in PostgreSQL and am using DBeaver 6.2.2.
When creating my first Test-DB I saw, ...
1
vote
1answer
52 views
Find Closest Route from single source to several different destinations in PostgreSQL
I've made network nodes in PostgreSQL SQL based on Osm2po. I can find the closest way among two points based on the pgr_dikstra algorithm but I want to find the closest route between one single source ...
4
votes
1answer
125 views
PostGIS erase/difference vector polygons from raster
I have a raster and a vector table in Postgres 12 PostGIS 3.0
The vector polygon(orange) overlaps the slope raster(grid in black, whiteish)
All I want to do is cut out/erase the orange polygon from ...
3
votes
0answers
58 views
Manage multiple users connecting on multiple Postgis database via QGIS
I have a question about QGIS projects and how to manage multiple users (fifteen users with usernames and passwords connecting to the same five databases in PostGIS).
Right now, there is one project ...
5
votes
0answers
136 views
ST_Covers fails on GEOGRAPHY column with GiST index, PostGIS 3.0 [closed]
I'm trying to upgrade to Postgres 12 with PostGIS 3.0. Unfortunately, I can't get my spatial queries to work. There seems to be an issue using ST_Covers on a geography column with a GiST index.
Proof ...
0
votes
1answer
19 views
Query to Map GPS points with multi-linestring
I have a column of geometry of type multi-linestring in a database as in the following format
Now I want to add a new column of speed by mapping GPS points against geometry column (as described ...
2
votes
1answer
90 views
Online Editing in QField (multi-user environment)
I have a QGIS Project that I run on QField to create points/lines and give them attributes in the Field. We have three tablets with the same project on it. Right now QField saves the new data "offline"...
1
vote
0answers
65 views
Get Corresponding OSM Node IDS from list of Lat,Long (CSV): Map Matching
I have a list of (lat,lng) coordinates. I want to map match them and get their corresponding OSM node ids? How can i do that using OSRM or any other map matching algorithm?
2
votes
1answer
54 views
How to retain the explicit geometry type of a column in a database view of PostGIS?
I try to retrieve "generalized" table's info for a long-term backup project from a PostgreSQL database version 11, with the PostGIS 2.5.3 extension under Freebsd 12.0.
I want to use the PostgreSQL ...
