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

learn more… | top users | synonyms

1
vote
1answer
36 views

Using negative radius buffer for ST_Buffer?

I am using postgis 2.2. I am using ST_Buffer to my polygon and it's working for positive radius (in meters) but when i am going to apply negative radius it is showing empty values. Here is my ...
0
votes
1answer
17 views

ST_AsRaster for polygon

Having a hard time to get a raster from a polygon ST_AsRaster(ST_Buffer(ST_GeomFromText('POLYGON((-30 40, -20 30, -25 20, -23 10, -30 40))', 4326), 50),ARRAY['8BUI'],ARRAY[118]); This line is ...
2
votes
1answer
37 views

Is there a way to automatically displace colliding labels?

I have a PostgreSQL table, which i'm loading on QGIS Essen, that relates many rows to the same object and all this rows have distinct values in the field i'm labelling the objects with. Hence, when I ...
2
votes
1answer
68 views

Clustering (ConvexHull) data based on distance

First I would like to mention that I am not an expert with PostGreSQL so my question may seem dumb, but here it goes: I have a database with over 800 000 points associated to a geometry (EPSG 2950) ...
1
vote
0answers
26 views

How can I style a feature that has multiple values for the same field?

I'm opening a PostgreSQL Table on QGIS which has multiple rows related to the same object (think of how you'd register distinct apartments on the same building). I do get to style it based on any of ...
0
votes
0answers
17 views

ST_AsRaster ST_AsTiff

Can someone give me an example how to use AsRaster and ST_AsTiff together? I am having trouble to create a tiff image. SELECT ST_AsTiff(ST_AsRaster(ST_Buffer(ST_GeomFromText('POLYGON((-30 40, -20 30,...
0
votes
0answers
15 views

How to get marker's coordinates from GoogleMap into ModelForm (GeoDjango)

models.py: class Location(models.Model): name = models.CharField(max_length=100, verbose_name=u"Локация", default=u'') coords = gis_models.PointField(u"долгота/широта", srid=4326, blank=True, ...
1
vote
1answer
19 views

How to update a line attribute in PostGIS using an attribute from the polygon in which it is contained?

I have a road dataset which I want to update using the speed information from the state polygon in which it's contained. for all roads (line) within a state (polygon) if the road speed is null road ...
1
vote
2answers
41 views

Select transposed values

I am trying to do a conflation. This is my method, and my problem: METHOD Step 1: I have two tables with road geometries. I am using ST_Dwithin to subselect geometries from table 'A' that lie within ...
1
vote
1answer
28 views

Find row with a polygon containing a specific lat/long

I have a Postgres table that has two columns – address, and geom (which is a multipolygon without any SRID set, but it should be 2263). I'm trying to find all of the rows that contain a given point, ...
1
vote
0answers
41 views

Overlapping polygons in the same table/ layer [duplicate]

I have catchment areas as polygons in a GIS layer. They were derived from two different sources (coded as "ws" and "dr" in a separate column named "use"). It's good to have these two sources as they ...
1
vote
1answer
39 views

What is the correct (QGIS) syntax for a masked PostQIS spatial query?

What is the correct syntax if I want to query a masked region from PostGIS from inside QGIS ? The data is from "planet_osm_poly" (imported via osm2pgsql) and I'd like to use a polygone from a ...
2
votes
0answers
36 views

How to count near intersecting polylines and merge them?

I have a dataset with several polylines created by GPS points of car trips. I am trying to create a simple heatmap where roads with more traffic (where polylines intersect) get darker. I already ...
5
votes
1answer
209 views

How to delete the small gaps (slivers) between polygons after merging adjacent polygons with PostGis

I try to delete the slivers that appeared when I have merge some polygons. I have two different cases: For the CASE 1, no problem I fill the gap with: CREATE OR REPLACE FUNCTION sliver_killer(...
1
vote
0answers
64 views

How to create cost maps based on OSM (landuse/…)?

I'd like to create cost maps (georef raster) for cross-country hiking based on OSM from pgsql/postgis plus height data. Maybe by creating multiple raster-files and the later merging them (lake=...
2
votes
2answers
96 views

Create GeoJSON with CRS from Postgis

I have a table of Multiline Strings and need to generate a GeoJSON with the properties turning to EPSG 4326. I made the SQL below but is not in the correct format GeoJSON. How do I create the GeoJSON ...
2
votes
1answer
51 views

How to create a QgsFeature with default attributes?

I have a polygon layer loaded from POSTGIS database. This layer has a default value for the first column. I can create a new feature set that attribute to some predefined value with newFeature = ...
1
vote
1answer
42 views

Speed up spatial query on large table on psql

How to speed up spatial query on large table of bytea. I tried the suggestion from this website to create an index using b-tree GIST but the index is exceed the maximum size of 8191. Any ...
2
votes
1answer
28 views

Load postgis vectorlayer with AuthConfigId

I've tried to add a postgis-vector-layer in qgis 2.14.0 by using authentication (authConfigId) and PyQGIS. When running the script I'm asked to login by username/password instead of using ...
1
vote
1answer
72 views

Select points based on value and proximity

I have a set of points in a table, each has a value between 1 and 100. How could I reduce this to the point with the highest value within a radius distance X. So that no point is ever closer than X ...
3
votes
3answers
80 views

Automatically refresh my wfs layer in Leaflet when adding data in Postgis

I’ve built a web-sig based on postgis, geoserver and leaflet. My polylines, …, are stored in Postgis and I call them through Geoserver in Leaflet as wfs layers like : var defaultParameters = { ...
1
vote
0answers
33 views

Cannot store raster in custom database schema

I want to store an elevation raster in a postgis database. However a problem occurs. I use the following command: raster2pgsql -I -C -e -Y -F -s 4326 -t auto dem.tif schemaname.filename | psql -U ...
0
votes
0answers
40 views

Creating curved lines between two points in QGIS

I am trying to create curved lines between two point in QGIS. There are a few posts that show how to create these lines (hyperlinks below). However, I am having trouble executing the code in QGIS. ...
1
vote
1answer
29 views

Convert data type from bytea to polygon psql

I was trying to convert data type of my column from bytea to polygon, ALTER TABLE polygon ALTER COLUMN geom type polygon using geom::polygon; But I got an error saying cannot cast type bytea to ...
2
votes
1answer
39 views

postGIS - Error: couldn't load library C:/Program Files/PostgreSQL/9.5/lib/postgis-2.2.dll: %1 is not a valid Win32 application

Environment - Windows10(x64), PostgreSQL 9.5(x64) I installed GDAL, PROJ.4 and GEOS (via OSGeo4W ) Then I installed postGIS via Stack App Builder (I have tried both versions - x32 and x64). In both ...
0
votes
1answer
44 views

ST_Transform error

I am getting an error from ST_Tranform while using ST_Intersection: No function matches the given name and argument types. You might need to add explicit type casts. select ST_Intersection(...
1
vote
0answers
34 views

Pg_dijkstra empty results

I am currently working on PgAdmin, and the pgrouting extension, in order to get the length and the path of the shortest path between every LNG terminal over the world (I have them as a table of points)...
1
vote
1answer
115 views

Finding all polygons inside of a polygon

How to find all the polygons inside of a polygon? I tried ST_Intersects and ST_Contains but they are booleans variables. I have a list of polygons in WKT format, so I am trying to pull all the ...
3
votes
2answers
72 views

Calculate distances between records in two tables

I need to identify the nearest 3 childminders (approx 500 records) to the nearest school (approx 120 records). Both the childminders and schools layers reside in PostGIS. Can anyone offer any ...
2
votes
1answer
55 views

How to split overlapping linestrings?

I'm new to PostGIS and a bit lost... I work with linestrings that sometimes overlap, for example: I'd like to distinguish these linestrings by splitting them like Google Maps do: I was thinking ...
4
votes
1answer
55 views

PostGIS Intersection weird behavior

I can observe a strange behavior with my PostGIS query. I am trying to accomplish the following: I have one polygon layer I have 2 different line layers layer with horizontal lines layer with 45° ...
1
vote
0answers
27 views

PostGIS Nearest Neighbor query performance - can it be quicker?

I want to canvas the community to see if my nearest neighbor query can be optimized at all. I am happy with my current results, but I'm always up for improving. I have 2 tables, lakes and towns. I ...
1
vote
1answer
49 views

Saving Edits on a WFS layer by using OpenLayers

So with an app that I've produced by taking this into account, I've managed to create a web map with editing function using a WFS layer from Geoserver. Problem is that, unlike in the working example, ...
0
votes
0answers
43 views

Counting nodata and data pixels using PostGIS

I'm using PostgreSQL 9.2.4 and PostGIS 2.0.3. I have some raster layer and I'm trying to get the count of all pixels (including nodata), cliped by polygon. Raster was imported by raster2sql: ...
3
votes
2answers
117 views

Measuring length through a polyline in QGIS.

I'm working on a fiber optic project. I want to measure the length through a fiber optic cable (polyline) from a point. Does anyone have an idea to help me? For example, I'm at the point 082 CTO and ...
0
votes
0answers
31 views

Postgis Database: How can I get all gps points between specified timestamps and specified region?

I am totally new to DBMS. I have traces of vehicles in different csv files for each user. Format: name,timestamp,latitude,longitude. 1) How to implement RANGE Query which asks for all the gps points ...
0
votes
1answer
23 views

PostGIS query with external bounding box

I am editing a shapefile as wide as an island. The data are stored in a Postgres/PostGIS database. I want to display only features from my defined bounding box. The bounding box itself is a custom ...
1
vote
1answer
70 views

How to add more vertexes to a LineString

I created a map of LineStrings using QGis and then it was exported to a Postgis DB, then I used the query below in order to create the vertexes in the map so it could be used with the functions of ...
1
vote
1answer
44 views

Why missing address IDs in postgresql/postgis query results?

I have two tables "ad" and "st" in my postgres DB. Table ad has 87 rows while st has 16,060 rows. Following code works perfectly to select st.geom (streets' geometry) within 50 meter radius with ...
1
vote
1answer
41 views

Correctly querying a raster mask only

I have a rasterA (whole Scandinavia, with values from 0-255) and a rasterB (in blue, a small part of south of Norway, entirely inside rasterA). I want to query the pixels of rasterA which are inside ...
4
votes
1answer
134 views

PostGIS Out of db VRT containing JPEG2000 GDAL 2.1 OpenJPEG

Build GDAL 2.1 with OpenJPEG Build PostGIS with above GDAL build Created VRT file with SENTINEL2 Driver / JP2 files. gdal_translate SENTINEL2_L2A:...
1
vote
1answer
42 views

Weird behavior with st_clusterwithin

I'm not sure I'm doing this right, I've two locations, one a point and one a point within an area. I'm running them through st_clusterwithin, and they both create 2 individual cluster groups (which is ...
1
vote
0answers
50 views

Mapnik3 + python3 + Postgis

I've installed mapnik3, python bindings, and postgis (and imported a pbf file into postgis), and can't seem to even make it generate a simple map as a png file. Specifically - where can I find ...
0
votes
0answers
20 views

Inserting markers coordinates into postgis table with one row per marker via php

I have a leaflet map with some markers (coming from a .gpx) and I’m trying to send them in a postgis table with one row per marker. For the moment it works but only for the first marker. Here is how ...
0
votes
1answer
39 views

How to create a line into a table in Postgis?

I need to draw a line and create a new entry on an existing table in Postgis. Say I have a table called myTable and two points with coordinates 528115,181037 and 528115,181037 using the EPSG:27700 (...
3
votes
2answers
57 views

How do partition tables improve query performance in PostGIS? [closed]

I have static road, country and state datasets in my PostGIS database. Road networks are partitioned by their containing country. Countries, States and other areas reside in their own table and are ...
1
vote
0answers
29 views

Interative raster with PostGIS

I'm searching some solution to show raster layers from PostGIS on a Web Page. I need a solution to build a interactive map showing the values of my raster data when the user to click on pixel of the ...
0
votes
0answers
20 views

Database modeling for big data (big set of points X big sets of possible values for each points)?

I'm building a PostgreSQL database with the intent to host modelisation results for different projects. Each project has a set of receptors points (~5000-10000 receptors) and have many results sets (...
0
votes
2answers
44 views

How to solve Geoserver editing in QGIS returning read only error?

After creating a table in Postgis, making it available in Geoserver and then creating the necessary stores and layers, I loaded the resulting WFS file in QGIS. When trying to populate the features ...
0
votes
1answer
25 views

Delete tags from planet.osm with osm2pgsql and minimize size of database

I want to import into PostGIS the whole planet but I am interested for certain tags. Specifically I am not interested in nodes, ways and areas which are related with the land. Below is a small part ...