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

learn more… | top users | synonyms

1
vote
0answers
23 views

Upload a raster in postgis

I know this might be a similar question to many others. I have been prompting multiple questions here and on google but I cant figure it out. I have a raster and I have uploaded it into postgis with ...
0
votes
1answer
79 views

PostGIS - Calculate of the average distance between points for a delimited area

I am working with PostGreSQL I have 2 data sets: 1) A points layer where each point represents a building. 2) A Polygons layer created from the points layer using convexhull and clusterwithin ...
0
votes
0answers
11 views

gdal_rasterize source connection problem

I am running psql on R. I am using PostgreSQL server set the connection to my database by dbConnect. I am trying to run gdal rasterize where it pick up the information from my database query result. ...
1
vote
0answers
23 views

3D time dimension (x,y,t)

I'm working with data points (x,y) and timestamp t as a separate attribute in my table. (x, y) are in WGS 84 latitude,longitude format while timestamp is in Unix Time. For instance (71.8, -13.5) and ...
0
votes
0answers
24 views

line_locate_point: 1st arg isnt a line

i used this query for routing procedure SELECT pgr_nodeNetwork('edges', .006); I am following this workshop http://workshops.boundlessgeo.com/tutorial-routing/ and this is the error, I can't ...
1
vote
1answer
43 views

ST_DWithin: Why does query not use the Spatial Index?

I want my query to output all street segments, that are within a distance of 20 meters of those segments which have the attribute clazz of 11 (motorways). I have this query from a user on this site: ...
0
votes
0answers
21 views

Getting ST_AsGDALRaster to recognize pixel type

I have several PostGIS raster bands in 32BF format. I am following the instructions here to access the band in Python as a numpy array. But it only works if I reclass to 64BF. This is my original ...
0
votes
0answers
17 views

ST_SetValues - int array

I am using ST_SetValues with array defined as ARRAY[[9, 9], [9, 9]]::double precision[][]. If I store 32bit int value in this array (like eg 65546) can I retrieve this value exactly? Or is there ...
1
vote
1answer
38 views

Failed once again to import shape file to postgis DB using ogr2ogr

Once again I try to import a shape file to my remote postgis database using og2ogr. Last time it was an Odyssey but I have managed to solve all the issues and report them one by one here. I have ...
0
votes
0answers
39 views

Creating a table and inserting selected streets using plpgsql functions

I am trying to select all streets within 50 meters distance from each address location and store them in a separate table using Postgresql (plpgsql) functions. So far, I have written the following two ...
1
vote
0answers
24 views

Setting default value in FileGDB table column during GDAL/ogr2ogr export from PostGIS?

I'm doing an export for one table (table name: 'schaden_polygon') from a PostGIS-database (db-name: 'mydata') to ESRI FileGDB via ogr2ogr commandline-tool. The PostGIS-table contains a column 'm_uuid' ...
2
votes
2answers
46 views

PostGIS: ST_DWithin: Why is the output empy?

I have just one table of street segments of Berlin (OSM Data, imported using osm2po, SRID set to 25833). I want my query to output all street segments, that are within a certain distance (e.g. 2000m) ...
0
votes
1answer
51 views

How to insert attributes into a WFS using OpenLayers?

So I have a web app that's running and is able to add points and lines to existing Postgis tables by getting data from Geoserver. I need the following one specific bit of functionality added: I want ...
1
vote
1answer
46 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
19 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
75 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
27 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
20 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
17 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
21 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
42 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
35 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
40 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
235 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
65 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
118 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
55 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
46 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
73 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
140 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
37 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
47 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
40 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
55 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
45 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
37 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
118 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
74 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
66 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
59 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
28 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
54 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, ...
1
vote
0answers
50 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
121 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
33 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
25 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 ...