PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

4
votes
0answers
105 views

Spatial query too slow to handle bigger OSM extracts

I'm trying to compute some statistics for OSM data using PostgreSQL 9.3.5 and PostGIS 2.1.4. I started with a small bavaria extract which I downloaded from Geofabrik. The db schema is the normal API ...
1
vote
1answer
76 views

How do I change the PostgreSQL data directory in the OpenGeo Suite 4.5?

I am using OpenGeo Suite and I would like to move the PostgreSQL databases files to a different drive than my operating system. The default location installed by OpenGeo Suite is in the ...
1
vote
1answer
97 views

QGIS 2.6 how to prevent user to drop a postgis table

Does anyone know how to prevent user to drop a postgis table? I have a vector layer imported from a postgis2.0 database, and one of my users drop it accidently. Fortunate I had a backup, but i don't ...
1
vote
1answer
52 views

Select Polygon Intersections Between Two Tables With No Common Key

I am new to geodatabase queries and cartodb. I am trying to write a SELECT query that will do the following. I have two tables. Table 1 contains congressional district polygons and info about ...
1
vote
1answer
31 views

SELECT geometry FROM spaces WHERE geometry ARE WITHIN OR OVERLAP polygons FROM OTHER table

I've got two tables, one containing LINESTRINGs and one containing POLYGONEs. I would like to select all the LINESTRINGs that are containing within the outer boundary of all the POLYGONs combined. ...
-4
votes
1answer
28 views

Make connection from Postgresql to QGIS? [closed]

I'm still new in QGIS. I want to add data from one layer to another layer in QGIS. How could I accomplish that?
1
vote
0answers
487 views

Using ArcMap 10.3 with PostgreSQL 9.3.x database and PostGIS 2.1.1?

I have a PostGIS database that we use for data storage. I would like to open this data in ArcMap instead of storing it in another format. In previous versions of ArcMap it has been possible to open ...
2
votes
4answers
163 views

PostGIS doesn't use spatial index with ST_Intersects

I imported the data from openstreetmap using osm2pgsql and copied it another table structure. If I made a query for containing rows using st_intersects, st_contain or someone else, postgis don't use ...
0
votes
2answers
84 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
227 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
80 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
66 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
90 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
61 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
39 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
87 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
327 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
201 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
48 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
51 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
33 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
48 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
78 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
155 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
110 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
109 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
57 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
91 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
82 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
92 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
75 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
48 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
85 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
87 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
113 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
44 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
62 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
23 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
218 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
74 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
68 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
43 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
42 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
60 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
35 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
121 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
58 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
282 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
61 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
89 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: ...