Tagged Questions

PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

2
votes
2answers
41 views

PostGIS SQL NOT EXISTS Statements? [closed]

I'm trying to query my PostGIS database for ID of people who did X but did not do Y. I've attempted the query below, but it does not return any results. I'm very new to SQL so I may be overlooking ...
1
vote
1answer
45 views

Accessing pgsql db set in cygwin from qgis installed on windows through osgeo4w, possible?

As the title of this question, I want to know if it is possible to access a PostgreSQL database set in cygwin (version 9) from QGis installed in Windows 8.1 through the OSgeo4W application, is that ...
2
votes
1answer
87 views

nearest facility with pgrouting

I have a postgres table of students with lat, lon and geom, , and a table of schools with the same, and a table of roads that has been converted to nodes. I'm trying to find the driving distance to ...
3
votes
1answer
54 views

How to quickly extend PostGIS's R-Tree implementation?

I am working on a problem where along with data points location, I also have information about the connectivity between points.(Think a graph of points, where an edge denotes some relationship) My ...
1
vote
1answer
25 views

Updating table with nearest node from another table

I have a roads table that has been transformed into nodes for use with pgrouting. I also have a table of geocoded addresses which contains lat, lon and geom. I am trying to write a query that ...
1
vote
1answer
59 views

Postgres/PostGIS: Dissolve multiple fields with COUNT (Point shape)

I have loaded point .shp feature classes into Postgres 9.3 db, and enabled postgis,plpgsql,postgis_topology extensions. I'm looking to dissolve multiple fields within a single table 'TABLE1' and add ...
7
votes
1answer
222 views

How do I calculate Manhattan distance with PostGIS?

I am using the ST_Distance function to calculate the distance between two geometries (a train station and a building) Since I know that all buildings and all train stations are in Chicago, which has ...
1
vote
1answer
61 views

Error inserting GeoJSON in PostGIS

I know there is a similar question but I am getting a different error and none of those answers have helped. I am trying the following query (the GeoJSON itself is from the PostGIS website. INSERT ...
1
vote
1answer
61 views

Adding SRID 3857 to spatial_ref_sys

My application is throwing the following error: DatabaseError: GetProj4StringSPI: Cannot find SRID (3857) in spatial_ref_sys I'm guessing (as stated above) that I need to add the SRID 3857 to the ...
2
votes
2answers
61 views

OpenGeo postgres installation not working from command line but works from PGadminIII on fresh install

Problem: I just did a default install of opengeo suite (4.1) on Ubuntu 12.04 and followed instructions exactly. The problem is that I can connect just fine to my postgresql through pgadminIII ...
2
votes
1answer
109 views

How to use pgsql2shp when PostGIS extensions installed in a separate schema as geometry

I have the PostGIS extension (postgis functions and spatial_ref_sys table) added to a specific schema called postgis. I'm wanting to run the pgsql2shp utility with a select query on geometries stored ...
1
vote
1answer
127 views

Simplifying Shapefile without risking Spatial Accuracy?

I am building a feature for a web application. Basically would use the ABS (Australian Business Statistics) data to render the regions on the Google Maps. I am able to implement the basic working ...
1
vote
0answers
34 views

How to dissolve inner polygons in a multipolygon

I'm trying to get the boundary of a collection of zip codes. Here is my current sql: SELECT ST_AsText(ST_SimplifyPreserveTopology((ST_Union(geom)), .003)) as poly FROM zcta5_data WHERE zcta5 IN ...
1
vote
1answer
86 views

QGIS Relation Manager

I have a PostgreSQL database with a one-to-many relationship. Here is a reduced example: CREATE TABLE buildings.building ( objectid serial NOT NULL, building_no character varying(40) NOT NULL, ...
1
vote
1answer
53 views

Implementing Wrapper of PostgreSQL?

How can I implement the Wrapper of PostgreSQL? I found this useful looking wrapper from a FOSS4G routing with pgRouting Workshop but I do not know how to implement it!
2
votes
0answers
38 views

Opengeosuite blank and postgresql errors

I had the unfortunate idea to upgrade opengeosuite from 4.0 to 4.1. I followed the docs found on opengeosuite site but something has gone wrong. I uninstalled opengeosuite and all connected packages ...
0
votes
0answers
48 views

Postgresql Postgis Query is taking long time

I have a table that included only 472 rows and geometry of table is Multipolygon. I am sending query to databse via PgAdmin III select * from mytable // this query takes 99413 ms this is vey long ...
2
votes
0answers
60 views

How can I merge adjacent polygons and return all (adjacent and not adjacent) as Multipolygon?

I have a postgres database with administrative boundaries and their geometry. Each of those boundaries have an ident number. What I want to achieve: First I want to select all rows which are ...
1
vote
1answer
82 views

GeographyFromText and ST_SetSRID+ST_MakePoint different results?

Why am I getting different results? SELECT ST_DISTANCE( ST_GeographyFromText('SRID=4326;POINT(13.401111 52.383343)'), --berlin ST_GeographyFromText('SRID=4326;POINT(9.997559 53.533778)') ...
1
vote
1answer
57 views

postGIS: what is more efficient: single or two-point search

I have seen different approaches to conducting searches. Which is the best approach in storing the data for database efficiency: saving both longitude and latitude points longitude latitude vs ...
4
votes
1answer
74 views

Binary insert into Postgis geography column results in value being inserted as geometry

When writing WKB as a value (and as a binary parameter) to a geography column, the persisted value is not geography. Example Java is also available here: ...
1
vote
1answer
95 views

Union of several linestring to multilinestring using MS SQL Server

Followed Postgreql query is the existing one used to combine serveral linestring to a single multilinestring. is there any equivalent of this in SQLServer . SELECT ST_AsText(ST_Collect( ARRAY[ ...
6
votes
1answer
153 views

How to get the centroid of a set of points in POSTGIS?

I use PostgreSQL with POSTGIS extension. I have a set of points in the_geom column from a table myshema.myobjects I would want create a select statement to get the centroid of this cluster, so from a ...
0
votes
0answers
42 views

Problem in importing .osm file with osm2pgrouting 2.0

i've got two different systems: First system is: Ubuntu linux 64bit Postgres 8.4.8 Postgis 1.5.1 pgrouting 1.05 osm2pgrouting 0.2.0 This the system i've used for an old project that is already ...
0
votes
1answer
100 views

Pgrouting install debian 7.5 whezy

I need to install pgRouting on a Debian 7.5 64-bit server. I have PostgreSQL 9.1 and PostGIS 1.5 running. But somehow I can not install pgRouting. 1 - To use add-apt-repository ran the command: ...
0
votes
0answers
55 views

Show graph in html popup for dynamic data of a postgresql table

I am trying to plot xy value from a postgresql database table. In the table there are multiple longitude and latitude corresponding to values. The table is like this, where all longitude and ...
0
votes
0answers
33 views

mysql foreign data wrapper for postgres with parameter substitution

I have created a mysql_fdw for my postgres and when i try to run the one with a subquerry that has parameter substitution it does create a table but on querying the foreign table it throws an error: ...
0
votes
0answers
75 views

How connect C# to Postgresql in host j.layershift.co.uk

I have installed postgresql database in http://postgres-project-1241043.j.layershift.co.uk/ host. I want to connect to the database using C#. I use Npgsql with following connection string. ...
1
vote
2answers
67 views

python qgis 2.4 : display a view with symbology

I have managed to display a view created using postgresql from qgis using python code: uri = QgsDataSourceURI() uri.setConnection("localhost", "5432", "stat", "postgres", "admin") ...
1
vote
1answer
120 views

Counting self intersections of linestring using PostGIS?

Is there a way of using PostgreSQL + PostGIS for finding the number of self intersections in a linestring? I have a linestring that goes forward but in circles, and I need to find the number of times ...
0
votes
1answer
39 views

Issues ST_MakePoint in PostGIS with Geoserver

I am trying to setup a GIS system to use PostGIS and Geoserver. I have loaded all of the data into a table. The data that is received has long/lat coordinates in Degrees, Minutes, Seconds, Direction, ...
1
vote
1answer
53 views

What is best way to reorganize osm into PostGIS database entity?

I'm just wondering how to reorganize the OSM given entity to more flexible and schematic purpose. The OSM gave 4 entities the planet_osm_line, planet_osm_point, planet_osm_polygon and ...
1
vote
0answers
64 views

Using python libary simplejson and urllib in PostGIS

I installed PostGreSQL9.3 and want to create a Pl/Python function using simplejson library and urllib. First I use OSGeo4W Shell to test the similar python script, it works. Then in PostgreSQL, it ...
6
votes
2answers
177 views

Growth of a PostGIS database for Social Media - Move to Cassandra?

I currently have a large and growing database of geo-social media. I'm using Postgres with postgis, and so far it's been quite good. My use case thus far has been to query the database for all posts ...
3
votes
3answers
191 views

How to rename PostgreSQL/PostGIS columns while importing Shapefiles?

I discovered shp2psql to successfully import geospatial data from ESRI Shapefiles into PostgreSQL/PostGIS. Now I would like to automatically rename the target column names. Is there any mechanism ...
3
votes
1answer
175 views

Debugging ogr2ogr “AddGeometryColumn failed” and “Terminating translation prematurely after failed translation of layer”

As noted in the title, I'm getting the following error: ERROR 1: AddGeometryColumn failed for layer pretty_polys, layer creation has failed. ERROR 1: Terminating translation prematurely after failed ...
0
votes
0answers
51 views

Calculate and Label the lengths of polygon edges/sides

I have got a map-server, postgresql/postGIS & Google map API implementation. Users will draw polygon features after logging in. I would like to Label the sides/edges of a polygon with length ...
0
votes
1answer
32 views

PostgreSQL Trigger on inherited tables [closed]

In my PostgreSQL I have a parent table and a number of child tables (inherited from the parent table). How to create a trigger which will be bind to all the current and future child tables derived ...
1
vote
1answer
87 views

How to render tiles from kml file

I'm trying to render tiles out of a KML file. Normally, I'd render tiles out of an planet.osm extract. For example, I'd select a tag using osmosis and then process it with osm2pgsql. Then I'd ...
4
votes
2answers
148 views

Optimize Tilemill export of project with contour-lines (and fix out of memory problems)

I have a project in Tilemill (based on OSM-Bright) that covers Europe and also displays Contour-Lines. My problem is that the performance of Tilemill is pretty bad when displaying the contour lines ...
1
vote
1answer
203 views

Converting file geodatabase with coded domains to PostgreSQL / PostGIS?

We have several file gdb's and would like to convert these to Postgres / PostGIS. We've used ogr2ogr to transfer these files; however, some of the fields are stored as coded domains and the fields are ...
2
votes
0answers
23 views

Convert ArcGIS gdb with coded domains to Postgres / PostGIS [duplicate]

We have several file gdb's and would like to convert these to Postgres / PostGIS. We've used ogr2ogr to transfer these files; however, some of the fields are stored as coded domains and the fields are ...
1
vote
1answer
80 views

Postgis create polygon extent/convex hull from spatial table

Please help, I have a polygon layer representing building footprints. I want to create new view in postgis that will contain rectangle representing bounding box and also another view that will have ...
1
vote
1answer
96 views

osm2pgsql missing coordinates

I am quite new to GIS and OSM and am trying to get my head around exporting custom OSM region and uploading them into PostgreSQL. I have started with a very small map area to export with only two ...
0
votes
2answers
69 views

How to update postgis layer from shapefile in QGIS?

My usual workflow with updating layer from shapefile was to: Open DB Manager; Load SHP files into QGIS; Open DB Manager; Import file (with drop option). Now my problem is that I have created ...
1
vote
1answer
52 views

Run PostGIS command line functions in pgAdmin?

Is it possible to utilize the PostGIS command line functions, e.g. shp2pgsql, from within pgAdmin III? I am using pgAdmin to connect to a remote server.
0
votes
1answer
34 views

How can I get a list of tables in my CartoDB account by size?

The CartoDB dashboard only sorts tables by date modified or created. I would like a list of tables sorted by size.
1
vote
1answer
77 views

Force St_split() to return geometries in specific pattern

I'm Using PostGIS st_split() to split a polygon geometry by a linestring. Then I use st_dump() to return each of the splitted geometry and it is working fine. But the issue is I want to force ...
1
vote
3answers
168 views

How to Build a graph from a map

I spent days looking in all questions here and trying tutorials. I need to build a graph for a region (It will be my input in simulation) I downloaded Postgresql and Postgis and osm2pgsql. Now i can ...
0
votes
1answer
126 views

ERROR: Operation on two geometries with different SRIDs

I'm new when it comes to GIS and PostgreSQL. I have searched everywhere for the cause of this problem but I can't find the solution. I was following the documentation here First i created a table: ...