FAQ Database Discussion Community


migrating from postgresql 9.3 to 9.4, postgis

linux,postgresql,postgis,archlinux
I upgraded from postgresql 9.3 to 9.4 and now I want to migrate my data. So this is what I tried to do. First I ran the old postgresql like so /opt/pgsql-9.3/bin/pg_ctl -D /var/lib/postgres/data/ start Then, I tried to dump the old database to a file: /opt/pgsql-9.3/bin/pg_dumpall >> old_backup.sql And...

Postgis declare a polygon from a text string

geometry,postgis
I would like to declare a regular polygon, in this case a square without duplicated points. The problem is that the following statement returns 5, but one would expect 4. SELECT ST_NPoints(ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')); But without the last point I get an error...

Will turning PostgreSQL database into PostGIS spatialy aware project and using it to store all web data?

django,postgresql,postgis
I'm starting my first website, and first big project and GIS is at the core. However I'm curious as to how will PostGIS change the PostgreSQL database. Will it make the database sub-optimal for storing everyday website stuff? Such as user information, messages, comments, callenders...ex. And are their any advantages...

NPGSQL: Relate Operation called with a LWGEOMCOLLECTION type

c#,postgis,npgsql
I have a query which fetches PostGIS data using Npgsql. Its purpose is to take a point (x,y co-ordinates) and work out which (if any) geometry is at that point. For the vast majority of geometries in the database the query works fine, but for at least one I get...

Not finding Oracle Connection when a PostGIS connection is defined

oracle,hibernate,postgresql,postgis,hibernate-spatial
I have an application running with Spring 3.1.1 , Hibernate 3.6.0.Final and Hibernate Spatial 1.1.1 on Tomcat 8.0.20. Until now, we were using Oracle 11.2.0.4 , but we want to migrate to PostgreSQL 9.2-1002.jdbc4 / PostGIS 2.1.6. We will migrate the database schemas on different phases, so at some stage,...

Display Data on Leaflet exclusively with Data from PostGIS

gis,leaflet,postgis
First of all let me say what this post is not all about. Am not asking about how to use PostGIS and Leaflet. I have that resolved in this post. What I am asking is a bit extension of the linked post. In the linked post when I show a...

Displaying PostGIS Data with Leafletjs

javascript,postgresql,yii2,leaflet,postgis
I'm learning GIS with PostGIS and I wanted to try something funny so I downloaded shape files from OSM, imported in PostGres with PostGIS extension and now I want to represent data from PostGIS visually. I have Pulled data with Query SELECT ST_AsGeoJSON(geom) FROM "dar-es-salaam_tanzania.land_coast; I got bunch of GeoJSON...

Postgis latitude/longitude read out reversed

ruby-on-rails,ruby,postgresql,activerecord,postgis
I have a class, Geocoordinateable, that gives models some useful methods for dealing with geo-coordinates. Included in this is "geocoordinates=" which accepts an array. https://gist.github.com/mboyle/58dd3add830bbdeef316 You can call "geocoordinates" to get the coordinates in an array: irb(main):056:0> b.geocoordinates => [-118.25, 34.197] When I assign coordinates like so: irb(main):058:0> b.geocoordinates =...

Postgres replace characters in string

sql,postgresql,postgis
I have column with text where I need to change characters! For example �ay----> need to be Day �rag---->need to be Drag So I need to replace � with character D. I try next but I get error:invalid regular expression: quantifier operand invalid update tableT pp set descript=(select regexp_replace(descript,'�', 'D')...

Retrieve all latitude longitude points within x distance given an area

location,gis,latitude-longitude,postgis,geo
Given an area defined by x amount of points, I'd like to split that area in to 3 mile blocks and retrieve the center latitude longitude from each block. Here's what I mean: Area covering London: Area split in to 3 mile blocks (not perfect): And then print out the...

ST_GeomFromText incomplete output

postgresql,postgis,pgadmin
Hi guys im into postgis from a couple of weeks. I've got a problem with this sql query (trying to make some polygons for exercise): SELECT ST_GeomFromText('POLYGON((13.399242 45.753833, 13.386882 45.780177, 13.392204 45.803039, 13.411258 45.815842, 13.426365 45.813569, 13.442844 45.788078, 13.399242 45.753833))',4326); the output i get is:...

NoMethodError using activerecord_postgis_adapter

ruby-on-rails,postgis,rgeo
Apologies if this is very newbie question, but I have tried to solve it for a couple of days with zero luck. I tried installing activerecord-postgis-adapter as per the README but am unable to create the model instance as I keep getting the error mentioned below on the code pasted...

error “illegal option --t” occus when import OSM map into postgis [windows environment]

postgresql,openstreetmap,postgis
I want to import OSM map into postgis, so used the following command in windows cmd console: osm2pgsql -U postgres -d osm -hstore -s _S ./default.style ./xxxx.osm But "illegal option --t" error occus: osm2pgsql illegal option --t Usage error. I don't know what's the meaning of "illegal option --t" and...

PL/pgSQL trigger to stop a river crossing another river

sql,postgresql,postgis,plpgsql,spatial
I have to write a trigger to stop a river crossing another river. I'm really struggling with it and any help would be appreciated. myriver is the table containing all the information on the rivers. So upon insert of a new river if it crosses an existing river, I should...

What is wrong with this ST_CONTAINS statement PostGIS - Find point in polygon

sql,ruby,gis,postgis
I'm trying the following: Event.where('ST_Contains(?,ST_SetSRID(location, 4326)::geography)', search_polygon::geography) add getting the error *** NoMethodError Exception: undefined method `geography' for but without that (::geography) I get a message telling me to cast, what do I do? HINT: No function matches the given name and argument types. You might need to add explicit...

Tilestache - Mismatch between the coordinates present in the PostgreSQL/PostGIS and those returned by PostGeoJSON provider

postgresql,postgis,geojson,tilestache
This is the relevant bit my Tilestache config, "points-of-interest": { "provider": { "class": "TileStache.Goodies.Providers.PostGeoJSON.Provider", "kwargs": { "dsn": "dbname=database user=username host=localhost", "query": "SELECT loc_id AS __id__, loc_name, geo2 AS __geometry__ FROM location", "id_column": "__id__", "geometry_column": "__geometry__" } } }, When I access - http://127.0.0.1:8080/points-of-interest/0/0/0.json I get the Response - { "type":...

Why is PostgreSQL doing a sequential scan instead of a index scan in this query?

postgresql,openstreetmap,postgis
I'm working with OpenStreetMap osm2pgsql database. One of its table (planet_osm_line) have two indexed fields: osm_id (int, primary key) and way (postgis geometry). I'd like to find which streets intersect with a specific street, which I know by it's osm_id. So I do: SELECT name, * FROM planet_osm_line WHERE highway...

Openlayers-3 WFS-T (Post feature to postgis via geoserver)

postgis,openlayers-3,geoserver,fiware
I have a problem with posting features from ol3 into postgis db via geoservers wfs.When i run this code, i only manage to insert gid(pk) and bin columns but the_geom(Geometry) column is empty. function addInteraction() { draw = new ol.interaction.Draw({ features: featureOverlay.getFeatures(), type: /** @type {ol.geom.GeometryType} */ (typeSelect.value) }); draw.on('drawend',...

BasicDataSource not finding postgresql or postgis jdbc drivers

java,spring,postgresql,maven,postgis
I'm trying to add a BasicDataSource to a new LocalContainerEntityManagerFactoryBean. When I create the new dataSource like so: <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="org.postgresql.Driver" /> <property name="url" value="jdbc:postgresql://localhost:5432/database" /> <property name="username" value="XXXXX" /> <property name="password" value="XXXXX" /> </bean> And add it to the entitiyManagerFactory <bean...

Psycopg ppygis select query

python-2.7,postgis,psycopg2,postgresql-9.3
I'm trying to setup a basic working postgis setup with python ppygis package. >>> import psycopg2 >>> import ppygis >>> connection = psycopg2.connect(database='spre', user='postgres') >>> cursor = connection.cursor() >>> cursor.execute('CREATE TABLE test (geometry GEOMETRY)') >>> cursor.execute('INSERT INTO test VALUES(%s)', (ppygis.Point(1.0, 2.0),)) >>> cursor.execute('SELECT * from test') >>> point = cursor.fetchone()[0]...

Using psql options in npgsql

vb.net,postgresql,postgis,psql,npgsql
I'm using npgsql in a small app used in the library I work for. Originally my app (vb.net) was using sendkeys.send and sendkeys.sendwait to type psql commands in cmd. This works but its slow and inefficient (ugly, buggy, etc.). I typically use the \o and \H options in psql to...

How to filter the max value and write to row?

sql,postgresql,postgis,greatest-n-per-group,groupwise-maximum
Postgres 9.3.5, PostGIS 2.1.4. I have tow tables (polygons and points) in a database. I want to find out how many points are in each polygon. There will be 0 points per polygon or more than 200000. The little hick up is the following. My point table looks the following:...

Get all points from a ShapeFile (.shp) in Java [closed]

java,postgis,arcgis,shapefile
I'd like to use shapefile (.shp) data in Java. A sample file I have looks like this: 0105000000010000000102000000070000003085EF7995215FC0E0A2FEADF6E648400439100E8D215FC0B04AF9E80BE7484048CC79E78C215FC0F885E4F20CE74840B86EEC078C215FC0E80213FC12E74840A4B69A9694215FC07011B67744E7484008E900F396215FC04867DE1949E7484004D90A7EA1215FC0A8F7E1AE4BE74840 How can I can extract/decode all points (lat/lon) in this shapefile in java?...

iterate and write separately linestrings (generate_series)

sql,postgresql,postgis,plpgsql
I have a table full of a mix of polygons and multipolygons and I would like to run a single function on to break them into LineStrings (or MultiLinestrings). My problem - I am currently returning a set with ... no geometry (?)... The function currently looks like this...(final based...

Can I store circle and polygon within the same spatially indexed column in postgis?

geospatial,postgis,postgresql-9.3,spatial-query,spatial-index
According to their documentation the spatial objects can be of the following POINT(0 0) LINESTRING(0 0,1 1,1 2) POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)) MULTIPOINT(0 0,1 2) MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4)) MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2...

Count number of points within certain distance ranges from another set of points

postgresql,postgis
I have the following, which gives me the number of customers within 10,000 meters of any store location: SELECT COUNT(*) as customer_count FROM customer_table c WHERE EXISTS( SELECT 1 FROM locations_table s WHERE ST_Distance_Sphere(s.the_geom, c.the_geom) < 10000 ) What I need is for this query to return not only the...

Size of data type Geography(Point, 4326) in PostGIS?

size,postgis,point,geography
Its constructor takes a POINT and a SRID. Since POINT is two doubles (8 bytes each), SRID is an integer (4 bytes), can one assume that the full structure takes 8*2+4=20 bytes of storage space?

Local nominatim openstreemap with Speed limits

openstreetmap,postgis,nominatim
I'm currently running a local copy of the nominatim maps of openstreetmap on PostGIS. Is there a way i can get the speed limits into this database so i can return that information as well. I've found that this data is available when querying openstreetmap but require this to be...

JPA Create Parent/child relationship with a lot of children

postgresql,ejb,postgis,jpa-2.1,hibernate-spatial
I am trying to store entity Track with children entities TrackPoints with JPA method create. However, to store Track with its children TrackPoints last really long - about 30 seconds. I tried GenerationType.Identity and GenerationType.SEQUENCE. If I also have Hibernate Spatial (Postgis) column, it lasts even longer - about 60...

Custom SQL for Geodjango on ForignKey

django,django-models,gis,postgis,geodjango
I have a following model: class UserProfile(models.Model): user = models.OneToOneField(User) location = models.PointField(blank=True, null=True, srid=CONSTANTS.SRID) objects = models.GeoManager() class Item(models.Model): owner = models.ForeignKey(UserProfile) objects = models.GeoManager() Now I need to sort the Items by distance to some point: p = Point(12.5807203, 50.1250706) Item.objects.all().distance(p, field='owner__location') But that throws me an error:...

Postgres preferring costly ST_Intersects() over cheap index

sql,postgresql,openstreetmap,postgis
I'm executing a rather simple query on a full planet dump of OSM using Postgres 9.4. What I want to do is fetching all ways which belong to the A8 autobahn in Germany. In a preparation step, I've created multipolygons for all administrative boundary relations and stored them in the...

Lat-Long of exits on a Freeway e.g US 101 say arranged in N-S direction

openstreetmap,postgis
I am trying to find all exits on US 101 exits, preferably in the order North to South using the OpenGIS osm2pgsql. But no luck so far. Closes solution I found is: http://stackoverflow.com/questions/1960005/how-to-get-lattitude-and-longitude-of-us-interstate-exits-programmatically comes close with the SQL: select osm_id, name, ref from planet_osm_roads where highway='motorway_link'; The query: select osm_id,...

PostgreSQL: ST_GeomFromText(unknown) does not exist

sql,postgresql,function,geolocation,postgis
Here is the problem, I just installed PostgreSQL right now and try to build a geoloc database. For this, i installed the PostGIS extension and PostGIS_Topology. In my pgAdmin UI, i can see my model, my schema is "Geo" and I can see below, in the functions list, the postGis...

Polygon insertion issue (due to SRID) on Postgres

postgresql,geolocation,geospatial,postgis,sequelize.js
I'm having trouble inserting a polygon into my table structure. I'm relatively new to PostGIS, so I may be making a pretty amateur mistake on this. My table is setup as "Regions" and I'm adding a column for my geometry: "SELECT AddGeometryColumn(" + "'public', 'Regions', 'geom', 4326, 'POLYGON', 2" +...

NOT EXISTS doesn't work

sql,postgis
I have a table countries, and I want to display all the neighbouring countries to Sweden, and I got two problems. My code is like this: SELECT b.cntry_name FROM countries as a JOIN countries as b ON ST_Distance(a.the_geom,b.the_geom)<10000 WHERE a.cntry_name='Sweden' GROUP BY b.cntry_name It returned a table looks like this:...

django-admin-bootstrapped messing with DateField, TimeField and PointField

javascript,python,twitter-bootstrap,django-admin,postgis
I've just added django-admin-bootstrapped to my project. Almost everything is fine. Actually there are two little bug whitch i'm wondering how to resolve. The first one is about DateField and TimeField : There is no date/time picker when i'm under django-admin-bootstrapped. When i check the source code, there is no...

Converting Geometry to long/lat

java,eclipse,postgresql,postgis
So in PostgreSQL I have a bunch geometry values for long/lat that are formatted like: 0101000020E610000095B9F94674CF37C09CBF0985083B5040 So in Postgres I can make a select query that formats everything Ex. SELECT ST_AsText(position_geom)FROM reports; So I have postgreSQL and PostGIS JDBC drivers installed in eclipse and thought I could do something similar...

Strategies to speed up access to databases when working with columns containing massive amounts of data (spatial columns, etc)

ruby-on-rails,ruby,postgresql,database-design,postgis
First things first, I am an amateur, self-taught ruby programmer who came of age as a novice engineer in the age of super-fast computers where program efficiency was not an issue in the early stages of my primary GIS software development project. This technical debt is starting to tax my...

Cannot get postgresql-9.3-postgis-2.1 on Ubuntu 14.04.1 Docker container

postgresql,docker,ubuntu-14.04,postgis,dockerfile
I tried to install postgresql-9.3-postgis-2.1 or postgresql-9.1-postgis-2.1 for a cloned app, but I can only get postgresql-9.4-postgis-2.1 on my Ubuntu docker image which is build from python:2.7 image. I looked into the image and found it's on a Ubuntu 14.04.1 image. I tries to install postgis on my Xubuntu 14.04.2...

Fluent NHibernate automap PostGIS geometry type

c#,nhibernate,fluent-nhibernate,postgis
Given the following model: using NetTopologySuite.Geometries; public class bounding_box { public virtual int id { get; protected set; } public virtual Polygon area { get; set; } } How do I automap the area property to a area geometry(Polygon) column when generating the DB schema using Fluent Nhibernate? Note that...