PostgreSQL is an open source object-relational database system.

learn more… | top users | synonyms (1)

3
votes
1answer
124 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
106 views

Moving SpatiaLite Table into PostgreSQL using ogr2ogr?

How can I move a Spatialite table from a Spatialite db to a PostgreSQL db using ogr2ogr?
3
votes
2answers
725 views

Importing .shp to postgreSQL

I want to import a shp file to postgreSQL. First I create sql file and then run PostgreSQL. To create sql file, I run this command in windows cmd: shp2pgsql -s 4326 worldCountries.shp worldcountries ...
2
votes
2answers
52 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
55 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 ...
1
vote
1answer
64 views

QGIS: Why do I get permission denied when saving style to postgres

In QGIS I am trying to save a style to a postgres database but I always get the error "Unable to save layer style. It's not possible to insert a new record into the style table. Maybe this is due to ...
3
votes
1answer
72 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
33 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 ...
3
votes
2answers
241 views

Saving rasters to postgresQL using raster2pgsql, saving forecast time

I would like to save a forecast which is saved as geoTiffs into a postgresQL database. I've been able to import the rasters using: raster2pgsql -s 0 -I -d tiff/field*.tiff -F public.latest | psql -d ...
1
vote
1answer
60 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!
4
votes
1answer
86 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
68 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 ...
4
votes
2answers
181 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 ...
7
votes
1answer
268 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
108 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
90 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
84 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
186 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 ...
2
votes
3answers
403 views

Stuck in loading raster to PostGIS

I am running running Mac OS X (10.7.5), PostgreSQL (9.2) and PostGIS (2.0.2) (installed via Postgres.app). The PostGIS extension seems to work perfectly with vector and raster layers. Once I enable ...
1
vote
1answer
131 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
36 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 ...
2
votes
0answers
48 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 ...
1
vote
3answers
173 views

How to set the tolerance value in pgr_createTopology function of pgrouting?

I am making a map based on the floor plan of a building. I made the shapefile and added it to the PostgreSQL database. I created the topology for the map using the following code: SELECT ...
4
votes
3answers
3k views

Installing Postgis on Ubuntu 12.04: unmet dependencies libgdal1

After sudo apt-get install postgresql-9.1-postgis I got that massage: The following packages have unmet dependencies: postgresql-9.1-postgis : Depends: libgdal1 (>= 1.9.0) but it is not going to ...
0
votes
0answers
74 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 ...
1
vote
1answer
86 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 ...
2
votes
0answers
91 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
117 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)') ...
6
votes
1answer
203 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
78 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
51 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
126 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

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
87 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
1answer
58 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
2answers
77 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") ...
6
votes
2answers
210 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 ...
0
votes
1answer
44 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
106 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 ...
10
votes
2answers
8k views

Adding shapefiles to PostGIS database

I was able to create a Postgresql database and then I executed, createlang plpgsql geospecies psql -d geospecies -f postgis.sql psql -d geospecies -f spatial_ref_sys.sql So now can I directly ...
1
vote
0answers
74 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 ...
1
vote
0answers
297 views

ArcGIS 10.2.1 crashes during connection with ArcSDE 10.0 inside PostgreSQL 8.0.1 [closed]

I'm currently working in an infrastructure built with ArcGIS 10.0 (Desktop and Server) [SP2 build 3200] and because for some functionalities (ArcGIS for Desktop extensions) I need to use a newer ...
0
votes
2answers
73 views

Osmosis, Save to different schema

Does anyone know how to use osmosis to save to a different schema? I used the pgsnapshot_schema_0.6.sql to create a blank data table, but it always creates it in the public schema. I need to create ...
0
votes
1answer
35 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 ...
0
votes
0answers
56 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 ...
1
vote
1answer
275 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 ...
1
vote
1answer
123 views

Geometry has incorrect latitude-longitudes

SELECT ST_SRID(geom) AS s FROM zones GROUP BY s; returns a single record: 4326 SELECT Find_SRID('public', 'zones', 'geom'); returns 4326 SELECT AsText(geom) FROM zones returns geometry like: ...
1
vote
1answer
105 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
3answers
152 views

Generating a routing database for the entire world while keeping the tags

I've been looking into this matter for a few days now, but I haven't come up with a working solution. Basically, what we are trying to do is create an entire world database, preferably in PostgreSQL, ...
1
vote
1answer
109 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 ...