PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

0
votes
2answers
68 views

Find closest points in routing network for a set of points to pick shortest route

I have the following WITH SQL Query which does select a set of filling stations along a route within the distance of 1000m. In the SQL statement I fetch 1 Point from the station and search the closest ...
0
votes
1answer
170 views

Get postal codes of a city in OSM via postgis/postgresql

So, I'm trying to get the postal codes of a city (polygon). Unfortunately, I never get consistent results with my code: SELECT a.name, b.tags->'postal_code' FROM planet_osm_polygon a JOIN ...
2
votes
1answer
68 views

am I setting up data from google geocoding correctly for ST_DWithin search?

Sorry to ask a basic question, but I have not been able to find this laid out entirely anywhere. I am mostly relying on this SO post: What Spatial Reference System do I store Google Map's Lat/Lng ...
1
vote
1answer
63 views

PostGIS returning a field one level higher in the Json

I have the following SQL on a PostGIS Database ($properties contains a list of fields, including osm_id) SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, ...
0
votes
2answers
70 views

Post and Reconcile buttons in versionig toolbar grayed out in Arcmap

I have a versioned GDB (Postgresql) and I have versions DEFAULT-> A -> B. Version A modifies two poly lines and version B modifies one of the two that is modified in version A. In ArcMap, even though ...
1
vote
1answer
58 views

How do I sum values that are geographically close to each other?

I have a dataset of Boko Harams rampage in Nigeria uploaded to CartoDB. I have columns like coordinates, dates, number of victims. Would it be possible to write a query that would sum the victims ...
0
votes
0answers
37 views

QGIS Crash: Enter Credentials

just wondering if anyone has come up against this issue...in the office QGIS will come up with an 'Enter Credentials' box with 'Error: Too many clients already'. When you enter your username and ...
1
vote
0answers
75 views

SQL query works in PostgreSQL but not in GeoServer SQL View

I can't find a solution for this problem after a couple of hours of research. The query is: SELECT searchaddress('some string'); FETCH ALL IN mycursor; The function searchaddress returns a ...
5
votes
3answers
277 views

PostGis: Geometry from UTM text

I have plain data in a postgresql table which looks like this: ╔════╦═══════╦═════════════╦══════════════╦══════════╦═════════════════╗ ║ id ║ index ║ easting ║ northing ║ utm_zone ║ ...
2
votes
1answer
154 views

ST_INTERSECT of each element of two tables (polygons, lines)

Im new to postgis and trying to solve the following problem: I have two tables: 1. containing polygons 2. containing lines I need to find out the id of all polygons on each line and fill them in new ...
0
votes
1answer
44 views

Finding lines of similar attribute with respect to nodes connected to its both Ends

I have line networks with node generated for Start and End Point . how to check if a line has correct attribute with respect to point created at its End. for instance FuID_Line = ABC_BDQ and Start ...
0
votes
0answers
42 views

Routes through centroids and array functions

I have a table stopcentroids4 like in figure with the following fields: gid, routes, geom and i want to draw a line for each route that runs through centroids. In my code there is a mistake. ...
0
votes
0answers
32 views

ST_Touches Function in Iterative Process

Im a complete newbie to postgis. I have the following problem: Got two tables: "mp_power_lines_new" -> contains lines of an electricity grid "uw.new"-> contains polygons of all substations in this ...
1
vote
2answers
40 views

How can I add extensions to PostGIS?

As newbie with PostGIS, I downloaded Postgresapp for macosx. Everything works fine, but I noticed some differences with Stackbuilder which adds to extensions address_normalizer and grouting. Can I add ...
0
votes
1answer
60 views

How to transform isolines to isopolygons with postgis?

I have a table a postgis table of isolines that's defined like this: CREATE TABLE myisolines ( gid serial NOT NULL, isotime timestamp without timezone, val numeric(10,4), geom ...
1
vote
1answer
122 views

How to create postgresql triggers to calculate geometry from x,y and vice versa at the same time

I have a postgis table with point geometry which looks like this CREATE TABLE mypoints ( gid serial NOT NULL, coordid integer, geom geometry(Point,4326) ); And a table with coordinates ...
0
votes
1answer
90 views

ST_AsGeoJSON function slow in PostGIS

I'm currently trying to pull neighborhood data from Zillow's Neighborhood page (http://www.zillow.com/howto/api/neighborhood-boundaries.htm) and then load it onto a leaflet map, openstreetmap. I ...
1
vote
0answers
95 views

how to automatically update column based on the second layer, trigger, postgreSQL

I am newbie in postgreSQL. I have 2 tables, observation and landcover, i want to create a trigger which would allowed to update the column observation.land based on the spatial information according ...
0
votes
1answer
49 views

geometries with different SRIDs errors (even after setting SRID)

I've looked at existing questions about this (including this one) and followed the advice that was shared there. I'm still seeing this when I run some queries: RuntimeError: Postgis Plugin: PSQL ...
0
votes
0answers
87 views

box2d spatial type

I'm storing data that defines a box. I created a column by adding it to my table as follows: SELECT addgeometrycolumn('grid', 'grid_geometry', 32650, 'GEOMETRY', 2); I then insert my data in using ...
1
vote
1answer
66 views

Basic sp-gist indexing?

Please forgive a very, very elementary question. I am using a PostgreSQL 9.3.5 database, and one of the tables stores longitude and latitude. (These are two separate columns.) I would like to do ...
2
votes
1answer
67 views

PostGIS replace pixel value by lookup table?

I was wondering if it is possible to write a PostgreSQL statement to replace the value of each pixel in an input PostGIS-raster with a value obtained from a look up table. For example, suppose I have ...
4
votes
1answer
65 views

Finding all segments that makeup a road in OSM

I've spent 2 days trying to figure this out and I'm afraid that it's something that's not possible. If that's the case then I'm thinking the wrong with with respect to OSM data. Let's say I have a ...
1
vote
2answers
45 views

Learning pg_routing - shortest_path error

I'm learning PostGIS and pg_routing to help in our project planning and grant applications at my transportation agency (we wanted to move beyond the limitations of the 1/4 mile walking buffer we use ...
1
vote
0answers
78 views

Create GeoServer SQL View with variable number of arguments ( double precison [ ] )

I was trying to create a PostGIS procedural function which will perform a particular operation depending upon the user's provided coordinates. But since the user can provide any number of coordinates, ...
2
votes
0answers
78 views

How do you disconnect ArcGIS Pro from a PostgreSQL database?

What is the equivalent to the ArcCatalog's Disconnect option in ArcGIS Pro? I have successfully connected to a Postgresql database, but cannot figure out how to disconnect. This is what I'm seeing ...
3
votes
2answers
99 views

pgrouting - network distance many-to-one shortest paths

I want to find the distance (km) between each origin point to the closest destination point (restaurants) using pgrouting. I have 3 tables my road network with nodes and edges 140 origin points - ...
2
votes
0answers
43 views

filling the attributes according to intersection of two layers (Postgresql)

How to automatically fill attributes of a point layer according to the exact position in the polygon layer? I mean how to write a command in PostgreSQL to automatically fill the attributes by the ...
1
vote
0answers
50 views

how Npgsql.EntityFramework support Spatial data

I am using Npgsql.EntityFramework + EntityFramework6.My database is postgresql. I Added to my C# project with this tutorial : ...
2
votes
0answers
22 views

What are the problems of loading overlapping (or the same) tifs into postgis?

I am still early on the Postgis learning curve. I have an existing table in Postgres - it looks like this: create table "public"."mytable" ( "rid" serial primary key, "rast" raster, "filename" ...
2
votes
2answers
173 views

How to change from EPSG:900913 to EPSG:3857 in Postgresql

I didn't use the '-E 3857' switch when importing the OSM data into postgresql using osm2psql. I'm trying to set up a map server for the first time and I'm following the instructions from here: ...
1
vote
1answer
69 views

How to select geography features within a certain distance given in miles?

I am just taking the first steps in using POSTGIS, but having trouble finding a simple example. I have a database with a Key field, lat and lon fields and a GEOGRAPHY field. I just want to have a ...
2
votes
1answer
64 views

Is it possible to use spatial function to retrieve non-spatial data?

I'm using PostGIS in postgresql.I wish to calculate points within the radius from particular geocode.For that i have created a table with spatial column geocode having datatype as geometry and ...
3
votes
1answer
39 views

PostGIS 1.5 Errors with PostgreSQL 8.4

I am trying to add PostGIS functionality to a database that I created in PostgreSQL using the following command: psql -d <db_name> -f /location/to/this/file/postgis.sql However, I receive a ...
1
vote
0answers
38 views

How to Increase the length of column type geometry in postgres(pgAdminIII)

I am just wondering is there any way to increase the default length of column type geometry in postgres. Since we are trying to save large number of vertices polygon in to the geometry column geom in ...
0
votes
0answers
52 views

Any way to speed up my postgis topology creation?

I need to do topology aware simplification, and I'm investigating PostGIS's new topology extension. However it's taking a very long time to create the topology, and I'd like to know if there's ...
0
votes
0answers
34 views

Upload SHP to PostgreSQL via QGIS

There was the error when I try to upload the SHP to the PostGRESQL. The feature is the line type. The error was: ERROR: Geometry type (LineString) does not match column type (MultiLineString) Do ...
1
vote
0answers
112 views

Query layer in ArcGIS 10.2.2 with PostgreSQL not querying data

After some days I was finally able to import OSM data to a PostgreSQL database and connect it with ArcGIS 10.2.2 I can load every piece I want by simple clicking "Add data ..." however a SQL query ...
1
vote
2answers
55 views

How to get the geometry value from coordinates

I have a table with empty geometry column. I have two columns in the same table which has xcoordinate and ycoordinate values. I want to get the geometry value formed by these two coordinates. The ...
4
votes
2answers
267 views

Why is the Google Maps circle shape not matching to the distance calculated with PostgreSQL?

I am running a PostgreSQL 9.1 Database with PostGIS 1.5 extension. Several coordinates are stored in there with type GEOMETRY (SRID 4326). I'm building a web-application with Google Maps Javascript ...
2
votes
1answer
55 views

How do I get the vector and raster metadata from a PostGIS database?

im using php/postgres/postgis in a webapp, im wondering if its possible to get metadata from the db about all shp/geotiff table like (name, projection, type(ship/tiff), date, desc, owner etc ...) i ...
1
vote
1answer
79 views

how to query contour postgres db for elevation at a point using postgis

Is there a way to interpolate between these contour lines for a given point using postgis? Thanks. I've come up with the following query to gather the potential contour lines of interest: ...
1
vote
2answers
71 views

postgresql rule to trigger

I have two spatial tables and I am mirroring the data between the two table with a postgres rule: CREATE OR REPLACE RULE create_centroid AS ON INSERT TO csg.client_area_request DO INSERT INTO ...
1
vote
1answer
70 views

Get closest (source or target) of an edge from a point in postgres

I have a Postgres custom function that uses pgr_dijkstra to generate a route working. It works be passing the lat/lon of the start and destination points. I then find the closest segment vertice to ...
4
votes
1answer
242 views

PostGIS doesn't use index for spatial query

I can't get PostGIS 2.1 running on PostgreSQL 9.3.5 to use a spatial index even for the simplest queries. The whole dataset is 8 million points (population count grid from here ...
0
votes
1answer
37 views

Modeling SQL Query to add statistical data to a geometry

Hello I have a problem modelling a SQL Query for the following problem: I have two tables with geometries from Shapefile der Verwaltungsgrenzen (WGS84). Of interest for me are Kreise and Gemeinden, ...
0
votes
0answers
82 views

How to split linestrings where start/end points don't touch nearby lines

I am trying to figure out some basic routing with PostgreSQL/pgRouting and I'm struggling a bit. Our network of linestrings are not topologically correct, tracks that should be connected don't touch, ...
1
vote
1answer
43 views

Is product purchaser in distribution area?

I am going to be using PostGIS 2 to create a lookup service. Given a point (lat/lon), I need to either find if a product can be distributed there, or b) a list of products or distributors. I am ...
0
votes
0answers
41 views

QGIS formatting problem using POSTGIS (datetime)

I want to use the time manager plugin using data from a PostreSQL-db. In there the time stamp is set up as follows: "[...]to_timestamp('2014-10-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS')::timestamp ...
0
votes
0answers
64 views

GeoServer 2.6 + PostgreSQL 9.3.5, GeoServer becoming unresponsive

We're displaying point vector data on a web client using an OpenLayers (2.12) WMS layer... rendered via a GeoServer SQL View reading from data in PostgreSQL. The particular layer has a viewParam on ...