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

learn more… | top users | synonyms

0
votes
1answer
29 views

Calculate average multilinestring point elevation

I'm triying to calculate the average elevation of some multilinestring points. Is there a fastest way than this pgsql function? CREATE OR REPLACE FUNCTION topstation.sig_moyenne_z(ingeom geometry) ...
0
votes
0answers
16 views

Connection Problem

I'm using PostGIS and then I'd like to make a PostGIS connection but I got this problem Connecting: host=localhost port=5432 user=postgres dbname=ASPIRE-PC Database connection failed: FATAL: ...
2
votes
0answers
11 views

How can I show photos in popup from a ByteA (postgis) in QGIS (or Google Earth/map)

I currently have a postgis database with a table containing point geometry of fire Hydrant. This table contains a raster_blob field which contains the Fire hydrant picture taken by field workers. ...
3
votes
1answer
91 views

Transform coordinate to EPSG:4978 fails

I am trying to transform a geometry to the EPSG:4978 projection. As the EPSG:4978 was missing in the spatial_ref_sys table, I have added it using the following query: INSERT into spatial_ref_sys (...
1
vote
1answer
33 views

Split multiple attributes from a big polygon to smaller ones

I am looking for a sort of spatial (un)join function. Let's say I have 2 tables, both with a large number of polygons. The first one has a pop/polygon column in his attribute table. The 2nd one has ...
2
votes
2answers
52 views

ESRI ASCII. Latitude and longitude in a in raster

I've started a new project that involves a ESRI ASCII grid. As far as i know it is a map of Germany or the UK (I'm not sure which one). This is how the header looks like: NCOLS 8083 NROWS 12952 ...
0
votes
1answer
23 views

postgis_tiger_geocoder nationscript.sh does not set the location of PSQL correctly

This generated nationscript includes the following line: PSQL=${PGBIN}/p (...) I have tried 'umpteen' variations of replacements, including the logical one: the physical path to the psql file. I am ...
1
vote
1answer
24 views

Drape multiple lines on a DEM with Postgis

I am trying to drape lines on a DEM by following this great tutorial: http://blog.mathieu-leplatre.info/drape-lines-on-a-dem-with-postgis.html When I do it on a single line by giving points ...
0
votes
0answers
32 views

Crop raster files when importing with readGDAL from DB

I have quite a lot of raster files stored in a PostgreSQL DB. Each of them has around 200MB and is representing EU-wide data. However I only need the the rasters to cover the area of Germany. I'm ...
0
votes
1answer
35 views

Write GeoJSON As Feature Object in Postgis

I'm trying to create a geofence/circle from a lat/long set and I'm close except that the GeoJSON that PostGIS creates differs in structure from the client library that our front-end uses. Here is my ...
1
vote
1answer
35 views

How to select first coordinates in multipoint or in collection used for point reducing?

I am using PostGis and I have geometry like this: MULTIPOINT(1673118.56314624 5778263.30559826,1672914.41092247 5778129.131443,1672384.7731665 5778105.44570288,1673513.70724225 5778525.74090022) ...
2
votes
1answer
48 views

PostgreSQL Shapefile import failed

I am very new to PostGIS/PostgreSQL. In fact, I downloaded it because of the answers I got to this question: Distance between centroid and farthest point of polygon I installed both PostgreSQL and ...
0
votes
1answer
20 views

Importing shapefile issue postgis. no import made or recognized reference

I am doing an exercise where I am about to load shapefiles into i PostGIS-database. I am doing it from an already created database and schema, this will mean that the only new item is the table that ...
-1
votes
2answers
74 views

ST_DWithin matches wrong points

The following query crosses every city called "London" with every "place" (both of which are in London, UK) and calculates the distance. The points are of type geography. SELECT c."Name", ...
0
votes
0answers
37 views

postgis iterate over rows (cursors)

I need to repeat the same set of operations for each road polygon by using an id (road_id): I have tested my code only on one polygon of geometry using a series of functions inside a WITH query: ...
1
vote
0answers
19 views

How to customize DB Manager default parameters

I'm using QGIS 2.10 and DB Manager plugin. I regularly use this plugin to display tables from a PostgreSQL - PostGIS database. I particularly use the integrated SQL Window to filter a table's content ...
-1
votes
1answer
64 views

Converting latlong into PostGIS geometry?

I have this excel spreadsheet with latlong coordinates in it and imported it into Postgis Problem is that I can't represent it on Geoserver because there is no typical Postgis geometry column How to ...
1
vote
1answer
33 views

QGIS Offline Editing Tool

I tried to test the QGIS Offline Editing Plugin but it appears not to work at all. I'm using qgis 2.14.3 running on a Lenovo Yoga 900 and Windows 10 on the client side. For the server I'm running ...
1
vote
0answers
30 views

PostGis - st_union polygons, keep sum of attribute

There are multiple My current query is: SELECT (ST_Dump( ST_Union( way ) )).geom AS way, 10000 AS way_pixels FROM ( SELECT way, way_pixels, ...
2
votes
0answers
14 views

PostGIS - raster - bit operations

I have a raster data in the database in separate rows. I would like to select 2 rows and do a bit operation with them (like |, &) and store the result in memory as a query output. Is this possible?...
2
votes
1answer
41 views

Default PostGIS type of geometry in SELECT

Is there an option in PostGIS (in query or table option) to set the return type of a Geometry? I'm using this query: select * from "locations" where "locations"."id" = 2 limit 1 And that will ...
0
votes
1answer
30 views

PostGIS Shapefile and DBF Loader - automatically Linked dbf file

This is my system: Windows 7 Postgresql 9.5 PGADMIN 3 PostGIS Shapefile and DBF Loader plugin I have imported data from a DBF file correctly with the plugin. My problem: This DBF file changes ...
5
votes
1answer
81 views

How to include aggregate field values of clustered points

I am creating a map that has point features representing things like campgrounds, picnic areas, boat launches, etc. In cases where many points are clustered in an area (representing amenities for the ...
3
votes
1answer
37 views

Unable to create field for PostGIS layer. PYQGIS

I'm having some troubles to create a new field in a postgis layer.This is the code I'm trying to use, but nothing happens, no field is created: from PyQt4.QtCore import * dbnam = "pruebapython" uri =...
0
votes
1answer
33 views

How to calculate the start and end lat/lon of a linestring which is not a part of a ST_buffer?

I'm trying to find out the lat/lon of points on a linestring that are not contained inside a geometry formed by a ST_buffer. I can find if the linestring intersects the geometry but I'm not able to ...
2
votes
1answer
34 views

QGIS or PostGIS auto-split long line features

I have a dataset of LineStrings in a postgis enabled database and would like to automatically split any feature longer than a certain threshold (say 1 mile). I am aware of the Split Feature tool in ...
1
vote
0answers
58 views

Adapting a ConvexHull fonction to a ConcaveHull one

I have a database with over 150 000 points in a EPSG 2950 projection. As I wanted to cluster those points in polygons, I used a ConvexHull fonction. The goal was to create clusters where I would find ...
1
vote
1answer
27 views

inserting points in postgis with pdo (php)

I have a problem with sql insert statement using PDO (php, postgis, postgres). I have tried to use different combinations, but none of them work. Here is what I ve got: $latitude= 46.07175; $...
0
votes
0answers
22 views

Using ST_* functions with different origin results in: Function not found

I've built a query which uses ST_asGeoJSON(); and If I run it on my server through pgadmin with credentials of UserX it runs like a charm. But when connecting through an webapp with other ...
1
vote
2answers
30 views

How to make a WFS Layer read only in Geoserver?

So I imported some postcode data into a Postgis table and published the layer using Geoserver. As it is postcode data, there is absolutely no need for users to edit, so I would like to make the layer ...
2
votes
2answers
30 views

Postgis meassuring distance between first and third points of a polygon

I am using ST_DumpPoints() function first to get the list of the different points in the polygon but after doing that, the first point is in one record and the third point is in another record and I ...
0
votes
1answer
31 views

How to calculate nearest point to a linestring in PostGIS?

I'm following this question to calculate POI nearest a road(linestring). I'm able to calculate nearest points in the linestring but I'm not able to find the distance from the POI to nearest point(...
-1
votes
0answers
15 views

ST_ColorMap in postgis color changes from white to red [duplicate]

Can you tell me how st_ColorMap in postgis should be constructed if I need color changes from white to red?
0
votes
1answer
26 views

ST_ColorMap problem in postgis

I run into ST_ColorMap problem in postgis. Here is the code fragment: r22=> WITH sfpoly AS ( SELECT ST_Transform(ST_GeomFromText('POLYGON(( 3727069.4991851943 4256930.979258035 , 4002242....
3
votes
2answers
72 views

ERROR: Only lon/lat coordinate systems are supported in geography

I'm trying to find out all the circles stored in the database(The circles are present all over the world) that intersect or overlap a given circle. I've stored the circles like this: CREATE TABLE ...
1
vote
1answer
18 views

greatest-n-per-group comparing geometry Postgis

I'm working on a query that I think could fit under the greatest-n-per-group category. So here's a simplified version of the table: So these are samples taken with a ~10 year span. So I have the ...
1
vote
0answers
31 views

How to visualize PostGIS data(geography) in QGIS?

I've created a table that stores spherical circles located all over the globe. But I'm not able to view this data in QGIS. This is my table: CREATE TABLE circles(gid serial PRIMARY KEY, the_geog ...
2
votes
1answer
39 views

Seeking updated pgRouting Tutorial?

Is there an updated pGRouting tutorial to this: https://anitagraser.com/2013/07/06/pgrouting-2-0-for-windows-quick-guide/ Trying to following it using QGIS 2.14.0 Essen + PostgreSql 9.5 (pgAdmin ...
1
vote
0answers
44 views

How can I remove only small inner rings in PostGIS?

I am looking for a way to remove small inner rings in a large polygon. I want to remove only the ones that are below a given surface and keep the bigger ones so that the the look (from a distance) is ...
1
vote
0answers
20 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
73 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
9 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
19 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
22 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
38 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
12 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
16 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
34 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
37 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
21 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' ...