Questions tagged [postgis]
PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.
7,547
questions
0
votes
0answers
52 views
Installing PostGIS
I installed PostgreSQL 12 on Ubuntu server 18.07. After the installation, I need to install PostGIS 3.
i run this command "sudo apt install postgresql-12-postgis-3".
The following packages have unmet ...
0
votes
1answer
18 views
Check the minimum pixel-value in a Raster using PostGIS
I'd like to check some basic statistic of a raster in my database. How can I determine the smallest raster value found in all raster tiles (enroaching all srid's)? My code looks like:
SELECT min(...
0
votes
0answers
26 views
raster2pgsql with tiling results in wrong pixel dimensions when tiles are st_unioned back together
Why is tiling messing with the pixel dimensions? I used raster2pgsql to create two tables test_hourly_1 and test_hourly_2 on the same source geotif. The first one doesn't use tiling and the second ...
0
votes
1answer
30 views
Populate field with drive time values during isochrone creation in Carto
I'm creating isolines (isochrones) in Carto to show 15, 30, 45, and 60 minute drive times from a set of points in a shapefile. Here is the SQL I've written to create those isochrones in an empty table....
3
votes
3answers
134 views
Making Polygon that covers the Northern hemisphere in PostGIS?
In PostGIS, I would expect ST_Geography polygons to use a clockwise winding rule, i.e. everything to the right of the edges of the polygon is inside of the polygon.
Therefore, I expected a polygon ...
0
votes
1answer
49 views
Add view generated with psycopg2 through sshtunnel as layer
I'm writing an internal QGIS plugin to query a remote database. I'm not administrating the server, but administrator has set up a virtual machine on a windows file server, with PostGIS set up.
I ...
1
vote
1answer
21 views
Replacement for pgr_pointToEdgeNode for pgr 3.0
I am working through the book 'pgRouting: A Practical Guide' and have come across a function that has been deprecated. The function is pgr_pointToEdgeNode. Is there a similar function I can use to ...
1
vote
1answer
43 views
Add an ESRI projected coordinate system to PostGIS
I was trying to add to PostGIS a new projected coordinate system, the ESRI World_Cylindrical_Equal_Area, 54034. I have searched for it in epsg.io but it is not available there. I would have liked to ...
0
votes
1answer
37 views
PostGIS 3 table join & classification with MapServer
I have been trying to create a mapServer map by joining 3 table then create classes on those joins (2000 - red, 2010 - green, 2020 - blue)
I am not getting an error but noting is display
$query = "...
1
vote
1answer
36 views
How to create line substrings with fixed lengths from a line ignoring inflection vertices in PostGIS?
I want to split a line in segments of a fixed length (e.g. 10m) like indicated in the picture. Using the approach of st_segmentize how-to-buffer-a-line-with-segments-in-postgis-using-unique-...
2
votes
1answer
38 views
Insert/Update/Delete table from another table changes in PostGIS
I'm trying to build a function that Insert/Update/Delete rows of a table based from another table changes. Unfortunately I could not continue, some problems occured:
DELETE isn't work correctly: Any ...
1
vote
0answers
36 views
How to buffer a line with segments in PostGIS using unique segmentsize?
I want to buffer a line in PostGIS. For this purpose I created a line, split it into fixed segments (8m) and run the buffer function.
--CREATE TABLE line(name char(10), geom geometry);
--INSERT INTO ...
0
votes
1answer
16 views
PostGIS & QGIS 3.4 : convert mixed Linestring/Multilinestring geometry to multilinestring
I have a PostGIS table in which the geometries are mixed Linestring and Multilinestring. But QGIS doesn't like it very much (it splits the table by the different geometries in the "add PostGIS table" ...
0
votes
1answer
20 views
Cut linestring segment at centroid in PostGIS
In a table of linestring three segments are connected.
first--second--third
could Postgis generate the centroid of second segment and merge first and third at center?
Is it possible?
first--...
-1
votes
1answer
38 views
MariaDB vs Postgres (POSTGIS) perfomance difference?
Has anyone used mariaDB for storing spatial data?
I am using postgis now.
My queries are mostly "finding peope within certain radius".
Happn app an app which shows you "the people you crossed path ...
1
vote
0answers
28 views
Best practice to upload/analysis raster vector data in PostGIS
I am trying to build a web-GIS platform where user can upload their data (raster & vector) and do some kind of analysis, like calculate zonal statistics. I read lots of articles and blogs about ...
0
votes
0answers
30 views
Best way to serve vector tiles from data that changes frequently?
I'm working on a project where I process national radar data and generate a GeoJSON with the data. The data is generated every 5 minutes and I delete the oldest data before I add the latest data. I'm ...
0
votes
0answers
69 views
Strange PostGIS errors after having altered its extension by specifying the postgis schema
Context
I'm trying to refine a PostgreSQL (v.10.12) trigger in order to actually execute some computations on point features only if the input points do intersect a convex hull stored in an other ...
1
vote
1answer
26 views
How to add more parameters to the sql file created by raster2pgsql
I'm using raster2pgsql to upload rasters to a postgres database
I execute the following command in cmd to create a sql that will add the raster
"C:/Program Files/PostgreSQL/9.5/bin/raster2pgsql" -s ...
1
vote
0answers
22 views
Raster grid on PostgreSQL 12.2/PostGIS 3.0.1 not able to add to QGIS 3.12
I'm trying to do the example on PostGIS Cookbook by uploading the Temperature Grid exactly as shown in the book (https://subscription.packtpub.com/book/big_data_and_business_intelligence/9781849518666/...
0
votes
2answers
34 views
PostgreSQL database designer that supports PostGIS geometry data types
Does anyone know if there are PostgreSQL database modeling software or apps that support PostGIS geometry data types? I know pgModeler is supposed to but I have been unable to get this installed.
0
votes
1answer
25 views
'ST_SEGMENTIZE' function in PostGIS gives single output
I try to split a line into 2m segments but somehow the function ST_SEGMENTIZE returns the whole line assigning just one id.
An example:
CREATE TABLE line(name char(10), geom geometry);
INSERT INTO ...
1
vote
1answer
44 views
ST_ConvexHull excludes some points
I'm trying to create convex hull for about 25000 points stored in the table.
ST_ConvexHull run successfully, but resulting geometry does not covers some points.
What could be a reason?
And it is not ...
1
vote
2answers
65 views
Vector layer from MapServer WFS not displaying in OpenLayers 6
I want to display a WFS layer in GeoJSON format on my map via OpenLayers. All my layers in WMS appear, only the one in WFS does not appear.
I did create a "WEB" block in the mapfile. And my layers are ...
2
votes
1answer
45 views
Performance optimization of ST_Touches
The query I am running is taking a considerable amount of time. I was wondering whether I am missing an opportunity to drastically improve performance.
My goal is to find out how many neighboring ...
1
vote
1answer
28 views
OpenLayers: Incorrect rendering of WKT
I'm using the draw function of OpenLayers for draw a polygon. I need the wkt from the drawn geometry because I need to send it to PostGIS.
<textarea name="name" rows="20" cols="80"
...
4
votes
1answer
78 views
Count number of points outside of Buffer?
I'm trying to count the total points outside of a buffer zone using PostgreSQL. The intention is to find all of the schools that do not fall within a 2 mile radius to libraries.
I've either gotten ...
0
votes
1answer
53 views
imposm3 on PostGIS to mapnik conversions
I am working with osm2psql and imposm3 to load a postgres db with OSM data.
The performance of imposm3 is seemingly far in advance of osm2psql (to a factor of 4 i am finding).
However I cannot find ...
1
vote
1answer
37 views
What is the geometry type if I am having mixed geometry?
I have a mixed geometry data some belong to MULTIPOLYGON and others belong to MULTIPOLYGONZM and I want to insert the data into one.
0
votes
1answer
44 views
How to Use ST_MakeLine query in leaflet to connect to Carto
I want to run in leaflet a query that works in carto Builder to connect points in a line:
SELECT ST_MakeLine(the_geom_webmercator) as the_geom_webmercator, min(cartodb_id) as cartodb_id FROM my_table
...
3
votes
2answers
30 views
Postgres: Remove Duplicate Rows returned by ST_Dwithin query
I am having trouble finding a query that returns one row for every unique vehicle crash that is within 100 feet of a select group of road features. My query currently returns a row for every collision ...
0
votes
1answer
33 views
GeoAlchemy get largest area within intersected polygon
I am using the world borders dataset to try and perform an intersection based on the buffer of a point. I need to return the country with the largest area within the buffer.
For example, in the ...
0
votes
0answers
66 views
Selection on PostGIS layers is… randomly working in QGIS
Context
Quick question...
Since the last minor update of QGIS 3, I cannot select features anymore on some (not all) PostGIS layers (see Fig.1).
Fig.1: On the left table, the selection is not ...
0
votes
1answer
11 views
Trouble working with a .TSV containing WKT
I'm working with a .TSV file that represents district boundaries in a portion of a country in Carto. The polygon data is represented in a string column of Well-Known Text lat/long vertices, which I'm ...
0
votes
0answers
41 views
Adding shapefile layer in PostGis creates two tables
When I try to upload a shapefile to my PostGIS database, it creates two tables (I can see them in QGIS). I import them by drag and dropping them on the PostGIS connection.
If I delete one of them, ...
1
vote
1answer
59 views
Update PostgreSQL table; insert data from subquery, on conflict do update duplicate id
Context
I run into (some good old) troubles when trying to update a PostgreSQL (10.12) table called nodes containing nodes with some few attributes from OSM + some few others.
I only want to update ...
0
votes
0answers
11 views
How to install pgRouting and use it in pgadmin4 in OSX
Is there a straightforward way to install pgRouting in my existing PostgreSQL(version 11) installation and use it through my pgadmin4 by creating an extension? I am using OSX.
1
vote
2answers
65 views
PostGIS returns an empty GeometryCollection out of an intersection between a MultiPolygon and a Point
Context
I've narrowed down an issue I faced involving many buffers and so on, to this in PostGIS (version 2.4):
SELECT St_geometrytype( St_geomfromtext(
'MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),
...
1
vote
2answers
67 views
Merge Linestring that intersects without making them MultiLinestring
I am working on PostGIS 2.5.3 and PostgreSQL 11.2
As seen in the example below, I have 3 LineString that I would like to Merge to form one single Linestring. The squares represent the vertices, so we ...
1
vote
0answers
45 views
PostGIS query to retrieve geometry that intersects both a bbox intersects and also matches other columns
I am relatively new to GIS, so have been searching around to try find a similar case to the one I am facing but have come up short.
Specifically I want to store multiple sets of geometry features in ...
1
vote
1answer
42 views
Create a clip in processing script
I am trying to create a simple script for me to clip the layers of a directory. The moment I create an output directory it no longer creates the interface of the tool. Additionally, I'm not sure I ...
3
votes
1answer
48 views
How to break a Linestring in n parts in PostGis
I have a Linestring shapefile, one of the columns of the table has a number inside (2, 6, 7, etc), I need to break the linestring into equal segments based on that field. For example:
If the ...
0
votes
1answer
46 views
PostgreSQL function return list of geometry
I have a query that return result as expected - list of points.
I would like to create a function with two parameters:
input polygon geometry
N as number of points to create
WITH
polygon AS
(...
-2
votes
2answers
51 views
Function like ST_AsGeoJSON but returning dictionary?
Is there some way for ST_AsGeoJSON to return a dictionary instead of a json-string? I want to use this value in another API, which leads to the string being escaped like this instead of being used as ...
2
votes
1answer
36 views
How generate ID uniquely in two featureStores with GeoTools?
I have a PostGIS datastore in GeoTools as follow:
JDBCDataStore dataStore = ...
and I have 2 tables(or FeatureStore in GeoTools) as follow:
SimpleFeatureStore table1Store = (SimpleFeatureStore) ...
1
vote
1answer
43 views
PostGIS - Add Column of Lines that Intersect Point
New to PostGIS. I have a road network which I have self-intersected/inner joined to generate nodes at each intersection.
I would like to add a column to the junctions table which lists all of the ...
1
vote
1answer
34 views
ST_Transform not working SRID
I have some MultiPolygon geometries in SRID 4258 and am trying to transform them into SRID 4326. When I run the following command, however, the output still seems to be in SRID 4258.
Command:
SELECT ...
0
votes
0answers
17 views
DGGS projections with PostGIS
Important DGGS projections (like ISEA4T, ISEA4D or ISEA7H) are free at https://github.com/sahrk/DGGRID
but I not see how to use it with PostGIS. Only maybe exporting grid... But ideal is a function ...
2
votes
1answer
55 views
Find all closest points to unique point in PostGIS
I have two tables valid_collisions (POINT) and intrct_pts (POINT)
I want to create a new table using a nearest-neighbor type of query to find aggregate values from my valid_collisions. In essence I am ...
-1
votes
1answer
46 views
To which extent can we use the data of healthsite.io - can we use maps etc. too? [closed]
To which extent we can use the data of healthsite.io?
Healthsites.io is a very interesting project which takes the collaboratively gathered data to a new level The latest project features along with ...
