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

learn more… | top users | synonyms

1
vote
0answers
20 views

PostGIS: Find nearest point given coordinates from another table

So I have two tables, one table is called stores which has the name of the store as well as the geom column that contains the coordinates for the stores. The other one is customers which has the ...
4
votes
0answers
30 views

Wrong coordinates after loading OSM to postgre

After loading OSM data for whole Europe to postgres (Osm2pgsql) I'm trying to add them to Geoserver, but the coordinates are totally wrong. The SRS is correct (4326), but the coordinate values are ...
1
vote
1answer
29 views

Adding more information to a PostGIS table via QGIS [duplicate]

I have a table within PostgreSQL/PostGIS that contains all point data. I need to add another large dataset to this existing table without overwriting the table. When I go into DB Manager within QGIS ...
2
votes
1answer
43 views

Visualize PostGIS 3D line geometry [closed]

I have PostGIS table with 2 geometries ( "GEOMETRY(MULTILINESTRINGZ)" and "GEOMETRY(LINESTRINGZ)" ). There are lines made just from 2 points - XY are WGS and both of them have random elevation Z (I ...
1
vote
1answer
33 views

Given a lat/long how to find the nearest node in a route calculated by pgr_dijkstra?

I'm creating a table route_<route_id> for the route given by pgr_dijkstra, like this. In my application, I have a websocket with a mobile client that it's sending it's current position every x ...
4
votes
0answers
48 views

Tolerance in PostGIS

I am working with spatial data in land management using Oracle. The daily task is to perform spatial analysis: defining the mutual disposition of land plots borders, territorial areas etc. The ...
1
vote
1answer
27 views

GeoDjango and PostGIS distance query efficiency concerns [closed]

For example, if I want a query that says "show me all houses within 50km from here and order them from nearest to furthest", these are the questions and concerns I have: Assuming we are using EC2 ...
4
votes
1answer
47 views

How to use “left” and “strpos” to get the left part of a string with an expression?

I have a table in QGIS with full names and I want to parse into first name(s) and last name. I’ve created the columns and am using Expression Builder to do this. Here's what I'm trying and it's not ...
0
votes
0answers
30 views

How to not display a layer, if its below a other semi transparent layer?

I have two polygon layers (PostGIS). One of them is semi transparent. If the other layer is below the semi-transparent one, it should not be displayed. My situation at the moment is: The grey ...
2
votes
0answers
43 views

How to pull a shapefile into R from PostGIS database (in Windows)?

I'm currently working on pulling data from a PostGIS database for the first time to map in R and need some help. The database contains a shapefile of countries/regions of the world. I am able to pull ...
1
vote
0answers
24 views

pgrouting pointAtoB function

Has anyone successfully implemented pgr_fromAtoB function? Sometimes I get 0 results from points in the same line string, sometimes I do get results, How can accuracy be improved?, or maybe do I ...
2
votes
1answer
45 views

Calculate data from a set of raster files

I need to make some calculations with data from raster files saved in my postgis database. I need, build a map, using the average from a set of these raster files, for example. Besides, I need to ...
3
votes
2answers
75 views

Simplify/generalize Polygons and keep topology of different layers PostGIS/QGIS

i have some polygons of a country, states, districts, communities and i want to simplify the geometry. i already simplified the geometry of each layer but afterwards of course it doesnt fit with the ...
3
votes
1answer
75 views

Distance in meters between two geographic coordinates

I want to determine the distance in meters between two geographic coordinates. The points are: Lat (deg) Lon (deg) A: -33.3930906 -70.5452765 B: -33.3993212 -70.5468767 So if i calc the ...
3
votes
3answers
82 views

Importing OSM files to PostGIS

I have installed QGIS 2.4.0 on Windows. I also downloaded an OSM file form Geofrabrik (SouthAmerica). I have been searching an option (plugin also) in QGIS for importing the OSM file to my PostGIS ...
1
vote
1answer
47 views

Display WFS (GeoJSON) from GeoServer with OpenLayers on Django

I want to display point data from PostGis to my Django application. I created WFS service from GeoServer and want to display using OpenLayers. I went through many posts but none of them worked My ...
0
votes
0answers
39 views

Replicate routing behavior

I've been dealing with problem for a while now and I want to know if I'm in the right track, basically I want to replicate this site functionality and so far I think it can be done with pgrouting ...
6
votes
2answers
86 views

What causes these geometry differences between original PostGIS data and Geoserver WFS?

Why does GeoServer (v2.5.1) WFS layer (see red line/layer below) not overlay features of source PostGIS table or related WMS layer in QGIS (tested in v2.6 and 2.12)? All layers are defined in the ...
3
votes
1answer
58 views

Postgis spatial index not used

I have the following table : Table "osm_france.france_2po_4pgr" Column | Type | Modifiers ---------------+---------------------------+----------- id | ...
0
votes
0answers
55 views

Interpolate missing M values along a line

Is there some way in PostGIS to interpolate missing M coordinates in a geometry like this? select ST_MakeLine(ARRAY[ ST_MakePointM (0, 1, 10), ST_MakePoint (0, 2), ST_MakePoint (0, ...
3
votes
0answers
28 views

How to quickly get coverage of multiple services at once using pgrouting driving distance

I'm trying to get some useful information out of openstreetmap data to choose where to rent an apartment in my city. I used osm2pgrouting and osm2pgsql to get OSM data into my postgis database. So far ...
0
votes
0answers
46 views

Select points greater than a certain distance from related points based on shared foreign key id

I have a PostGIS-enabled PostrgeSQL db, using QGIS, with data using EPSG:4326, and projected as EPSG:29903. I have a table with points with pk point_id and a table with images with fk point_id. I want ...
6
votes
1answer
79 views

Adding PostGIS layer from QGIS Python plugin

I try to develop a plugin to add postgis layers to qgis from a plugin as part of learning python. The code for adding a postgis layer is taken from the cookbook and work fine if I run it from the ...
2
votes
0answers
40 views

display shapefile from postgres using arcmap?

I have uploaded shapefile using postgis extension in postgres but when i tried to display the shapefile in arcmap, it wont display the data in vector. this is the image of my connection. i followed ...
1
vote
0answers
70 views

Visualizing shapefiles on web using SHINY package of R with PostGIS?

I am using PostGIS database to store my shapefiles. Now I want to read those files through shiny package of R. How can I visualize those files on web using shiny package of R and perform statistical ...
1
vote
1answer
58 views

Convert 1m to decimal degrees (WGS84) depending on polygon location

I have a table with small polygons that are scattered around a "continental" area (Brazil). How can I convert 1m to decimal degrees of a wgs84 reference system (longitude / latitude) depending on the ...
3
votes
1answer
42 views

Force ST_collect to return only a GeometryCollection

In the postgis documentation it is mentioned that ST_collect returns a MULTI* or a GEOMETRYCOLLECTION. But how can I force the function to only return a GeometryCollection not depending on it's input ...
2
votes
1answer
86 views

Create my own MapBox Vector tile and self hosting

I understood hosting mbtiles is possible and some solutions are available. I not sure that I can find the answer; How can I create my own vector tiles (mapbox vector tile format) which from my data ...
0
votes
0answers
36 views

pgRouting adding point or line barriers

In pgRouting can we add barriers (point, line, polygon) to avoid using a road partially not entirely. For example we have point features in another table. Or line features which is sub-part of a ...
1
vote
1answer
27 views

Use Postgis to convert wkb_geometry data type to geom datatype

How would I write an ALTER statement or otherwise to convert an existing imported data column from " wkb_geometry geometry(LineString,4326)" to a geom data type? I ask because I've moved to ...
0
votes
0answers
44 views

Clipping raster without create new table in PostGIS?

I'm learning about PostGIS, and I would like to create a SQL to cut a raster with a shapefile to work in my R program. I managed creating a new table, but I would like build it without a new table. ...
1
vote
1answer
68 views

Nearest Neighbour function

I'm trying to create a function which calculates the nearest neighbors under a certain length in a table s_1 an stores them in an output table. This is what I've got so far: CREATE OR REPLACE ...
2
votes
1answer
81 views

Improve performance on a st_dwithin query (in PostGIS)

At the momenent I am working on a query, as described in an earlier question. I have the two tables testme (tracked GPS-profile with Point Geometry) and roads (geometry roads shapefile) Besides ...
3
votes
0answers
27 views

Mapnik: Parameterize queries for PostGIS datasource?

My datasource is PostGIS, but I need to build the query on the fly based on user-supplied filters. Obviously I don't want to create an SQLI vulnerability, so I'd like to parameterize my query, e.g., ...
0
votes
1answer
31 views

How do I rename an out db POSTGIS raster

I used the following command to import a bunch of raster to postgis out-db style (only pointers to the rasters are stored in the database): raster2pgsql -s {srid} -c -R -I -C -F -t auto {file_name} ...
1
vote
1answer
53 views

Clipping raster objects in PostGIS

I'm trying to cutting a raster object (from a PostGIS table) using a shapefile with reference, extracting raster data from a polygon area. My objective to work with the clipping raster into R. I'm ...
4
votes
1answer
71 views

How to create trigger to check geometry validity?

I want to create trigger to check geometry validity in spatial table. I have a table: CREATE TABLE vec_geometry ( id bigserial is_valid boolean, invalid_reason character(128), geom ...
2
votes
2answers
63 views

ST_Distance loop

I want to create a postgresql function which calculates the distances between random points who are stored in one table. For the Table: CREATE TABLE s_1( id bigserial primary key, the_geom geometry, ...
1
vote
1answer
55 views

In PostGIS: how to split linestrings into their individual segments?

I have one table with many linestrings and I want to make a new table containing the individual segments of all the linestrings, no matter if they are grouped line per line or not. This is my ...
1
vote
1answer
39 views

Create a line from an array and measure its length with PostGIS (ST_MakeLine, ST_Length)

do I understand it correctly that it is possible to create a line out of an array with the function ST_MakeLine()? I would like to create a line from several points (more than two) and then calculate ...
4
votes
2answers
58 views

Get the 2 points immediately before and after a given point in PostGIS

In the picture below: The given point is C, I would like to select A and B, but not Z (which is closer to C than B but it's not immediately after in the LineString). Note that C is always ON the line ...
1
vote
1answer
47 views

How to create polygon from bounding box coordinates (PostGIS/cartoDB)

I have data to import to cartoDB in CSV format in which there is a bounding box column holding two lat/long coordinates representing the corners of a bounding box, like ...
1
vote
0answers
58 views

How to calculate multiple distances between set of points and vector grid in PostGIS?

My set contains the following input data: A set of points and a hexagonal (vector) grid. For each cell in the grid, I want to calculate the driving-distance to each point. I thought this would be a ...
3
votes
1answer
81 views

after truncating a table in postgres, why does qgis lose track of updates/features?

I'm running a python script which drip-feeds data slowly into a postgres database with postgis extension. I'm using autocommit, and committing one row at a time. Horrendously slow, but I need to do it ...
2
votes
1answer
39 views

ST_Contains return wrong results

I have problem with ST_Contains This query return false, but it have to be true. SELECT ST_Contains(ST_GeomFromText('POLYGON((38.19715082645416 56.51095701071156, 39.20248601585627 55.9670496196438, ...
1
vote
1answer
29 views

“polygon” on sphere postgis geography

I have a set of points defined by their longitude and latitude, and I would like to check if they lie within a "polygon" on sphere. By polygon on sphere, I mean area in between 4 lines going between 4 ...
7
votes
1answer
144 views

Georeference raster in non horizontal plane

We have raster data which is generated from (vertical) walls. We would like to keep this data in a postgis database and have the spatial reference encoded "the most accurate way". Currently, they are ...
5
votes
2answers
116 views

Remote connection to PostGIS?

I have recently setup PostgreSQL and PostGIS on a debian server on a local network in my home, which is working perfectly. I am able to access the db from other machines on the same network using the ...
4
votes
1answer
45 views

PostGIS point distance from polygon not 0 when on edge

I have a pretty simple problem: SELECT * FROM ST_Distance(ST_GeographyFromText('POLYGON((70 -40,70 -39,71 -39,71 -40,70 -40))'), ST_GeographyFromText('POINT(70.48 -39)'), False) returns ...
3
votes
1answer
86 views

How to remove spikes in Polygons with PostGIS

I'm trying to validate the topology of a shapefile. I managed to repair cases of overlap and gaps, but i can't resolve cases of spikes. The image shows one example of this situation. I used the ...