Questions tagged [postgresql]

PostgreSQL is an open source object-relational database system.

Filter by
Sorted by
Tagged with
7
votes
2answers
9k views

What Spatial Reference System do I store Google Map's Lat/Lng in

I am new to PostGIS, Spatial Reference Systems and projections and want to store lat/lng coordinates retrieved from Google Maps and Openstreetmap and its services like the Geocoder/Nominatim. I'm ...
7
votes
2answers
2k views

Creating Vector MBTiles?

I am searching for a tool to generate vector-tiles that I can then display using the Mapbox Map-View. We currently have a postgres database with all of our data. What is the best tool to generate ...
7
votes
2answers
2k views

vehicle tracking system in postgresql performance

I am developing the vehicle tracking system using postgresql. When the data comes from device to my DB server, I am calling the following functions after inserting data into log table. Distance ...
7
votes
1answer
6k views

qgis to local postgis connection - fails

It seems pretty clear I cannot connect to a local postgresql installation out of QGIS. This is my first experience with PostGIS. I am using PostgreSQL 9.0 on a win7 machine. I can see the successful ...
7
votes
1answer
2k views

ST_Union fails with TopologyException despite valid polygons and using ST_SnapToGrid

I am working with Postgres 9.6 and PostGIS 2.2.2. These are my tables: Table "public.allparcels" Column | Type | ...
7
votes
1answer
5k views

How to USE ST_difference AND ST_intersection in case of multipolygons? (PostGIS)

I'm trying to use ST_difference and ST_intersection with two layers of multipolygons. I want something like: With the input on the left and the result on the right. MY DATA: t1.geom contains 1000 ...
7
votes
2answers
3k views

How can I cluster all points in a postgis table together based on their distance from each other?

I have a PostGIS (in postgres 9.3) table which has Points. I want to figure out the "clusters" in the data. If 2 points are < X distance apart, they should be added to a cluster, and this should be ...
7
votes
3answers
1k views

How to do a spatial join in PostGIS that returns on the minimum value of point intersecting polygon?

I have a polygon table (buffer) and a point table (dem_points), and I want to do a spatial join on them. I need to join the point with the minimum elevation (dn) to its spatially corresponding ...
7
votes
2answers
748 views

Does PostGIS have separate implementation of GiST index from PostgreSQL?

I want to know if PostGIS has a separate implementation of GIST index or it reuses the GIST index implementation provided by Postgresql?
7
votes
3answers
6k 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 ...
7
votes
2answers
2k views

How to check if points are within a circular sector using PostGIS?

I want to get the stars within the black region as shown above. To find this I have the centre of circle (lat, long), radius and the angle of region (45 degree). I am new to postgres. I worked on ...
7
votes
3answers
551 views

What is the easiest one click install method for PostGIS and it's entire dependencies?

For someone new to database servers and their dependencies, what is the easiest assured way of installing PostGIS on a machine with no other software beyond what comes with its operating system? I'd ...
7
votes
1answer
6k views

Connect to Postgresql ArcSDE 10.1

I cannot connect to postgresql 9.0.5 with ArcSDE 10.1, using ArcCatalog 10.1 on Win 7 professional 64 bit. I followed the steps described here, and installed the Pg-Agent from Stack builder. When I ...
7
votes
3answers
4k views

Geometry issues when creating table from existing spatial table

In short, I want to split an existing spatial table into two parts. The split itself is straight forward. I start with this (fully functioning) table: CREATE TABLE table ( my_identifier smallint, ...
7
votes
2answers
1k views

Basic PostGIS and PGRouting question

I'm using OSX 10.6 and just downloaded and installed PostgreSQL 9.0.2-1 and pgRouting 1.0.5-1 for Postgres 9.0 from here. I'm a confused what my next steps are though. I'd like to try loading a ...
7
votes
2answers
2k views

ERROR: could not load library “C:/Program Files/PostgreSQL/11/lib/rtpostgis-2.5.dll”: unknown error 126?

I have got this message when I wanted to create a new extension. How can I solve this problem? I have Win10 64 bit and PostgreSQL 11.
7
votes
1answer
7k views

Could not load library “C:\…\ rtpostgis-2.2.dll”

I've installed PostgreSQL with Postgis (tried both version 9.4 and 9.5 ) on a remote desktop running Windows 7 64bit. However, when I executed this SQL command: "CREATE EXTENSION postgis" (on ...
7
votes
2answers
2k 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 ...
7
votes
2answers
2k views

How to use interacting PostgreSQL/PostGIS triggers in QGIS to create multiple labels on polygons?

EDIT: The trigger functions posted are well done but there are still a few issues. I've uploaded two screen recordings: http://workupload.com/file/ygRnYtp9 http://workupload.com/file/OBY7i9Fh I ...
7
votes
3answers
4k views

Developing Web Application For Map [duplicate]

I have huge data of maps usually most of the table are co-ordinates and the other are raster maps stored in SQL server 2008 and PostGres Server. I am using desktop application to analyse the data. ...
7
votes
3answers
3k views

How to load OSM data into PostGIS preserving all Tags?

I am new to the field of GIS and am trying to view source tags in OSM. I have downloaded the shapefiles from http://downloads.cloudmade.com and have loaded them into Postgres. When I view the data, I ...
7
votes
4answers
10k views

How to fix “Access to database denied” when accessing PostgreSQL?

I got a tiny problem regarding connection to my server. What should we do when this message appears in front of you? I've done several changes in the pg_hba.conf entry but yet to succeed. Any ...
7
votes
2answers
2k views

Support for PostgreSQL / PostGIS Inherited Tables in MapInfo/QGIS

Does anyone know if MapInfo and QGIS will support inheritance in a PostgreSQL/PostGIS database? I would like to make use of inheritance but need to make sure the software I use will work with them. ...
7
votes
1answer
4k views

How to Find points in a Kilometre radius

I am new to PostGIS and GeoData. I need to find where the lightning has stroke by a period of time and kilometre radius based on a given location. I have given lon/lat and my table is: latitude ...
7
votes
1answer
2k views

raster2pgsql: Strange error when creating a table

When I try to load an ASC file into my psql setup, I get a strange error message. I have created the SQL input file via raster2pgsql -s 4236 -I -C -M gis_data.asc -F public.lightning > lightning....
7
votes
2answers
5k views

Can't import OSM data to PostGIS, permission denied

I use Postgres 9.1 on Xubuntu 11.10. I've created a db 'aus' and user 'aus', granted ALL on the db to the user. Now I try to import the data, and it outputs this error: $ osm2pgsql --create --...
7
votes
1answer
1k views

PostGIS and invalid SRIDs

I'm having difficulty with a PostGIS query. I am trying to query my database (world_countries) using a bounding box drawn on a google web map. The objective of this query is to find any countries that ...
7
votes
2answers
7k views

Why is PostGIS query not using spatial index?

I am having an issue with a spatial query that is not making use of indexes. My schema is: CREATE TABLE geoplanet_place ( woeid integer NOT NULL, "name" character varying(300) NOT NULL, ...
7
votes
2answers
1k views

How to get an array of simple lines from one multipoint linestring in PostGIS?

How can I extract some LINESTRING consisting of 3 or more POINTs to several LINESTRINGs each of 2 POINTs in PostGIS?
7
votes
5answers
6k views

Using PostgreSQL with ArcGIS

Have mercy ... with Stackexcachange I'm not yet so familiar, so this is initially somewhat opaque. I try to do my best to organize this thread as well as possible. Recently I've started to get WFS ...
7
votes
2answers
10k views

What does 'DBMS table not found' message mean?

I have a plain postgressql table (version 8.2). I am trying to connect to it to it from ArcGIS desktop 10.0, and use as a query layer, as given in this document: http://help.arcgis.com/en/...
7
votes
2answers
847 views

Do large vertex counts cause performance issues?

I have a table of several thousand tuples - each tuple has a geometry column. The problem is, these geometries have a large vertex count. The max vertex count is 400, while the average is 200. ...
7
votes
1answer
6k views

Output polygon data from postGIS and read it on Google Maps API

So, I have a group of polygons stored in PostGIS. I'm trying to build a web application with Google Base maps (using Google Maps API). Now, How do i output the polygons in the right format so that ...
7
votes
2answers
445 views

Memory considerations for cloud based GeoStack

Some time ago I asked about hardware considerations of cloud based GeoStack implementation. As a follow up to this question There are some good pointers in related question already, but they mainly ...
7
votes
1answer
311 views

Replacing corporate GIS? [closed]

I'm looking at replacing our corporate GIS, this is the first time that I have had the responsibility of such a task despite my years of experience with the software as an end user. We will be ...
7
votes
3answers
478 views

Are there any open source Python modules compatible with arcpy that will allow complex SQL queries?

I am trying to handle string (non-geometry) duplicates in a large attribute table of a shapefile without having to convert to gdb and back or connect to a postgres db. I need to be able to use a count ...
7
votes
1answer
1k views

Spatial index vs two coordinates indexes

I have a table with two columns: latitude and longitude. I want to get all objects inside a "rectangular" (well, rectangular in a lat/lon coordinate metric) bounding box: min-max latitude and min-max ...
7
votes
5answers
2k views

ArcSDE installation with PostgreSQL (error - 1015)

I am trying to install ArcSDE for Postgresql on CentOS 5.5 in i686 architecture. I have successfully installed and configured Postgresql. I did SDE pre-installation and as ArcSDE user, run the ArcSDE ...
7
votes
1answer
1k views

SQL “Case” statement when count(*) is 0

I am trying to write the following SQL command using case clause. with x as (select the_geom as geom from table1 where st_dwithin(the_geom, st_setsrid(st_makepoint(28.959495,41.019913), 4326), 0....
7
votes
2answers
4k views

Direct connect to PostgreSQL without ArcSDE? [duplicate]

I have postgreSQL installed on my machine.My machine already has ArcGIS 10. No Sde installed. Can we create geodatabase in PostgreSQL with out installing ArcSDE? Jay Cummins is right. I am intended ...
7
votes
2answers
2k views

Deleting small gaps (slivers) between polygons?

I try to delete the slivers that appeared when I have merged some polygons. I have two different cases: For the CASE 1, no problem I fill the gap with: CREATE OR REPLACE FUNCTION sliver_killer(...
7
votes
1answer
607 views

TileMill fails to load Europe data from PostGIS. What am I doing wrong?

The Problem: Using OSM-Bright in combination with an imported (osm2psql) Europe shapefile, tiles won't load unless I have 2-3 layers active (of the total of 29). I don't have this problem with an ...
7
votes
1answer
351 views

Create a Shapefile from SQL dump file

I am trying to map data by querying a table called xyz. I need to show the the_geom data on the map. I have below query in my MapServer .map file: DATA "the_geom from xyz using unique id using SRID=...
7
votes
1answer
194 views

PostGresql and PostGIS courses in Canada

I have been looking for good professional courses for learning more than just the basics for PostGresql nd PostGIS. I have found a number of good courses, but they seem to all be in the US. I was ...
7
votes
1answer
163 views

Finding similar trips in a database

I have a trips table and a bus_stops table. The trips table has a from_stop_id and a to_stop_id. The bus_stops table has lat and lng information for each bus stop. How can I order the trips in ...
7
votes
1answer
1k views

How to include aggregate field values of clustered points

I am creating a map that has point features representing things like campgrounds, picnic areas, boat launches, etc. In cases where many points are clustered in an area (representing amenities for the ...
7
votes
2answers
6k views

Loading a raster using QGIS into a PostGIS2.0 enabled database

Following on from my attempt to install a postgresql 9.1 db with postgis 2.0 on windows 7, I am trying to figure out how to load a raster. I have successfully loaded a shapefile, and am trying to do ...
7
votes
1answer
920 views

How to load layers from postgres using SELECT statement?

I am trying to load a postgres table (columns extracted using a select statement) from the python console in QGis: The following works fine and the layer loads as expected: uri = QgsDataSourceURI() ...
7
votes
1answer
373 views

GROUP BY on geography in postgres 9.4.0 returns wrong/different results

table definition: CREATE TABLE search ( id bigserial NOT NULL, location geography(Point,4326) NOT NULL, location_aggregated geography(Point,4326) NOT NULL, created timestamp with time zone ...
6
votes
3answers
3k 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 ...