Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgis]

PostGIS is an extension for the PostgreSQL object-relational database that adds support for geographic objects.

1
vote
0answers
42 views

PostGIS: how to speed up casting to geography? [closed]

I have a very slow query and isolated the problem to casting my geometry type to geography. Here is my query, which basically calculates a bounding box of 15 meters (hence the need for casting to ...
0
votes
0answers
23 views

GeoDjango vs SQL query gives different results when filtering points in the polygon

I have a Django model with the following field: class PropertyPost(models.Model): ... location=models.PointField(null=True,blank=True) ... I am filtering the objects.locations in PgAdmin against ...
0
votes
1answer
26 views

How to debug PostGIS connection issue from MapServer?

I have a PostGIS connection string that works fine in GDAL, as I get a raster response when running gdalinfo "PG:host=... port=5432 user='...' password='...' dbname='weather_models' schema='rasters' ...
0
votes
1answer
30 views

left join returning one more row than expected

I have a table of single polygons (table rast_poly) derived from a raster and a table of polygons that represents buffered points (table pt_buff). The raster polygon geometry has been reduced by using ...
2
votes
1answer
44 views

Buffer view that calculates numbers from a different layer

Basically people draw lines and from these lines, I need to have a view that will create a buffer that will calculate potential revenue from those lines mylinetable is a table with a portfolio_id ...
0
votes
2answers
127 views

QGIS 3 - Creating a new layer through DB Manager

When I load a polygon shp file into QGIS 3.6 and run a query finding the centroids of the polygons, I cannot load it back to the layers panel. The same operation works fine in QGIS 2.18.2. The query ...
0
votes
0answers
31 views

View GeoJSON data after clicking on the OpenLayers map

I'm trying to make a piece of code that when I click on the map to return GeoJSON property data that I clicked on. I tried this way: map.on('click', function(e){ //console.log(evt); //...
1
vote
1answer
34 views

Filtering points against polygon

I am able to run the following polygon filter against my points and it will correctly filter out my points in POSTGIS="2.4.4 r16526" select propertypost_propertypost.* From propertypost_propertypost ...
0
votes
1answer
22 views

Request a Ajax with GET to remove one or more geometries in OpenLayers

I have a html selection of the form: <table class="table"> <tr> <td> <label>Afiseaza:</label><br /> <select id="layer" onchange=...
2
votes
1answer
30 views

MapServer Raster WMTS From PostGIS Column

I'm going to guess this isn't possible, since I haven't seen documentation supporting this in either MapServer, PostGIS, or GDAL. I have a PostGIS table with two columns -- timestamp and rast -- ...
0
votes
1answer
51 views

Getting geohash of LINESTRING in PostGIS?

I have some LINESTRING in PostGIS. LINESTRING(104.011 30.637,104.014 30.638,104.022 30.640,104.026 30.645) LINESTRING(104.022 30.666,104.031 30.659) LINESTRING(104.044 30.655,104.056 30.655) I ...
2
votes
2answers
82 views

ST_Buffer in PostGIS produces different results for the same set of lines

We have a single LineString (a route on a map) made of multiple points (blue line) which we are then buffering to produce an area around it. We have found that everything looks as expected unless the ...
0
votes
0answers
24 views

TopoJSON from PostgreSQL database

I already finish input and run all the GeoDjango code, it worked. But time after time my data got bigger and result in big size of GeoJSON. I used this code to read database from PostgreSQL (PostGIS) ...
0
votes
1answer
13 views

Loading GeoJSON by AJAX method in OpenLayers, centering the map automatically

I'm trying to center the map on the geometries that are on the map. To encompass all but until now I have not succeeded in how ... It centers further than geometries :( if (ol.proj.proj4 &&...
1
vote
1answer
75 views

PostGIS snap line segment endpoint to closest other line segment

I have a PostGIS table of singlepart line segments that I've run the ST_ChaikinSmoothing function on. Smoothing all of the line segments worked great, but these simplified geometries created small ...
1
vote
1answer
51 views

PostgreSQL PostGIS: Spatial join but keep all features that don't intersect

Spatial joining a point table to a polygon table, creating a new output table. So far I can only get the output table to contain features that satisfy the spatial join. However, I want the output to ...
1
vote
3answers
63 views

How to find the closest polygons to a point

I am trying to the find the four closest building polygons to a junction point layer. I understand how to obtain this for a single point but am unsure of how to specify the four closest polygons for ...
0
votes
0answers
33 views

ODK Aggregate PostGIS and MapServer WFS with attached images

I am using ODK Collect to collect ground data. The data collected are stored into a PostgreSQL/PostGIS database then I publish them as WFS through MapServer and read them with Leaflet (Leaflet-WFST ...
2
votes
0answers
74 views

Add Line Layer from PostGIS to Leaflet

I have a line layer in PostGIS that I would like to add to a Leaflet map. I have the PHP setup, but I can't seem to get the Javascript correct. I know my issue is converting the line to geoJSON. The ...
0
votes
0answers
9 views

How to insert raster and other columns using raster2pgsql?

I would like to have a table with a few more columns than just the raster data. Let's say I'd like to have anotherColumn. I tried raster2pgsql -s 3857 -R -a -k -f public.table.image -q -e -F -X ...
3
votes
2answers
90 views

Convert polygon with filled region inside hole into multiple polygons

Suppose you have a polygon with an inner ring representing a hole, and inside that hole is another inner ring (an "island"): The above shape can be generated using: SELECT ST_MakePolygon( ...
2
votes
0answers
40 views

Route creation with specific length and elevation change [closed]

I'm currently working on a project for my computer science study. The goal is to make a service that allows the user to enter a distance and elevation change which will then result in a random route (...
1
vote
1answer
31 views

Easiest way to draw a lot of features over a base map (transparent tiles)

I'm working on a project, where I have to visualise a lot of lines (and dots) on a map. We used to use openlayers, our own opensteetmaps tile server for "background" maps, and then generate a GeoJSON ...
1
vote
1answer
37 views

PostGIS SQL Query Filter by Bounding Box

I have the following query on a PostGIS Database SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM ( SELECT 'Feature' ...
1
vote
1answer
30 views

PostgreSQL: Spatial join points to polygon, listing point attributes where multiple are joined

I have a large polygon table and a large point table in a postgis enabled PostgreSQL database. Each point has a unique reference number (double type). I want to spatial join the points to the polygons,...
4
votes
1answer
42 views

Exporting layer to PostgreSQL / Inserts per Transaction

I am exporting layers to a PostgreSQL 11 database with PostGIS extension. The export itself is taking a while for big datasets (let's say 50.000 points). As I see in the pgadmin dashboard there are ...
5
votes
1answer
111 views

PostGIS function to move a polygon to centre over new point coordinates

Background: (Note: I am trying to do as much of this as possible in PostGIS/PostgreSQL) I have a PostgreSQL table containing location data of an aircraft in orbit around a city. Every second a new ...
1
vote
1answer
37 views

Clipping and merging tiles using GDAL based on bounding box from PostGIS? [closed]

I have a set of polygons in PostGIS DB. I also have a set of GeoTIFF files. I would like to know what is the best way (using GDAL) to get one polygon from DB (based on SQL Query) and then take all ...
3
votes
2answers
79 views

Find what polygons are not fully covered by union of polygons from another layer in PostGIS

I have two tables in a PostGIS database >>> select postgis_version(); "2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1" One represents the country borders and another one is administrative ...
2
votes
0answers
40 views

PostGIS ST_Area causing error when used with “use_spheroid=false” setting, succeeds with spheroid

I am running into an issue with using the PostGIS ST_Area function against a dataset loaded with osm2pgsql. Setup: PostgreSQL 11.2/PostGIS 2.5.1 osm2pgsql 0.96 When I attempt to run the ST_Area ...
0
votes
1answer
53 views

Using ST_Intersect in PostGIS? [closed]

I am a beginner with PostGIS. I would like to know if it’s possible to use Count or “>” with ST_Intersect. Which districts sit in 3 neighbourhoods ? SELECT district.name FROM district JOIN ...
2
votes
1answer
58 views

ST_Transform not working locally

I am using PostgreSQL 9.6 and PostGIS extension 2.4.4. On my machine, I get an error while running ST_Transform query: SELECT ST_Transform(ST_SetSRID(ST_MakePoint('27.9252', '-26.0987'),4326),32619) ...
1
vote
1answer
52 views

PostGIS st_intersects with exceptions

I have a query in PostGIS using st_intersects like this SELECT "name", "id_punto", "geometry", "properties", "type" FROM "points" AS "points" WHERE st_intersects("geometry", 'POLYGON ((-103....
1
vote
1answer
106 views

Handling AIS data using Python and PostGIS?

I want to turn points into a linestring in PostgreSQL with the extension of PostGIS using Python. I want the lines in a new table because every few minutes more points are added and it needs to keep ...
-2
votes
2answers
64 views

How to gently ask PostgreSQL to add PostGIS extension

I have recently installed PostGIS for the first time. I'm trying to use psycopg2 to create some polygon features. This runs: cur.execute('CREATE TABLE test_insert_shape (uuid varchar PRIMARY KEY, ...
1
vote
0answers
64 views

asyncio with multiprocessing

I am trying to run a multiprocessing on a zonal statistic in pure Python. I am new on it. I have to apply a zonal statistic with a shapefile with more than 6 million features on a taster of 6 GBs (...
2
votes
1answer
29 views

pgRouting assign source and target value to streets based on available nodes

I am a beginner in pgrouting. I have two tables in my postgres database. A street table, where each segment (see red line) carries an Id (ogc_fid). Additionally I have a nodes table (see brown points ...
3
votes
1answer
60 views

ST_ApproximateMedialAxis returns multilinestring instead of single linestring

ST_ApproximateMedialAxis on POLYGON returns MULTILINESTRING. Shouldn't it be just single LINESTRING? We use Postgres 11 + PostGIS 2.5.0 (Geos 3.7.0), SFCGAL 1.3.6 (CGAL 4.13), pgrouting 2.6.1. ...
1
vote
0answers
29 views

Cycles in LineString in PostGIS table?

I have a LineString table in PostGIS, which represents wiring in streets. Where the geometry column represents the wiring lines; firstpoint is the first geometry point (cable segment); lastpoint the ...
0
votes
0answers
19 views

Prevent Export/Save As of Vector Layers/Shapefiles

I have created a database using postgreSQL and created multiple users assigned with different roles Some of the users can only select and view the data and not update or delete any record. It works ...
2
votes
1answer
30 views

return only one value per point from ST_Value() - currently returning 2 values for some points

I'm testing a point sample query with a subset of 5 points on a single band elevation raster. Using my usual approach (which I've previously not noticed this behavior), I get six records returned: ...
2
votes
1answer
52 views

How to get data source attributes from PostgreSQL and PostGIS layers

I want to get attributes like schema name and table name from PostgreSQL and PostGIS loaded layers in QGIS 3. I've searched in API docs but just found about set data sources, anyone knows how to get ...
5
votes
3answers
283 views

Calculating shortest distance between polygons?

I have many polygons stored in a table. I want to filter out polygons where they separate too far away (say 1km). My approach is: calculate the minimum distance between a particular polygon to other ...
1
vote
1answer
29 views

OS Translator plugin replaces the unique feature references (TOIDs) with ogc-fid which is an integer

I've successfully loaded OS MasterMap Topography GZs files into my PostgreSQL (9.5) database, using OS Translator II plugin within QGIS (2.18.18). But somewhere during the translation process the ...
1
vote
0answers
46 views

line_interpolate_point: 3rd arg isn't within [0,1] strange behavour

I have a strange berhaviour in this postGIS query: SELECT st_length(st_linemerge(v.geom)), c.prog_ini, c.prog_fin, c.prog_ini/st_length(st_linemerge(v.geom)) as ini, c.prog_fin/st_length(...
2
votes
1answer
36 views

shp2pgsql uses deprecated addGeometryColumn function

It is very likely that I am misunderstanding the documentation as I'm new to PostGIS, but it says here regarding the "addGeometryColumn" function that since version 2.0.0 of PostGIS: This function ...
0
votes
2answers
138 views

ERROR: parse error - invalid geometry (Postgis)

Initially I was storing my column as path and then following this question I changed it to text. I'm trying to convert my column from text to geometry type by running this command: ALTER TABLE ...
2
votes
2answers
123 views

Azimuth calculation from One to Many Points in PostGIS

I have vector data of 3D City Model in PostgreSQL database. So from my data geometry column, which is a surface geometry. (These are geometries of all the surfaces of buildings). I had extracted the ...
1
vote
1answer
41 views

PostGIS view on multiple geometry columns

I have two PostGIS tables, ‘filelinks’ and ‘maricopa_plss_qtrqtrs’. The filelinks table has a geometry type column named ‘geom_p’ and the maricopa_plss_qtrqtrs has a geometry type column named ‘geom’. ...
0
votes
0answers
50 views

Using the geometry calculator to create lines between points in QGIS 3.4?

I have a postgreSQL database layer (with a postgis extension) that updates every 5 minutes and is creating points of the current location of ships. I can't seem to figure out how to connect the points ...