Postgres replace characters in string
update tableT pp set descript = (select replace(descript, '�', 'D') from tableT where id = pp.id) Why don't use replace?...
Why is PostgreSQL doing a sequential scan instead of a index scan in this query?
postgresql,openstreetmap,postgis
Rewrite your query so that instead of having a sub-query within ST_Intersects, you instead have a cross join in the FROM, which will then be restricted by the intersects in the WHERE (which also implicitly does a &&, ie, bounding box check, which will hit the spatial index). SELECT name,...
NoMethodError using activerecord_postgis_adapter
As of version 3.0.0 you can't set column specific geo factory properties. And the method set_rgeo_factory_for_column is removed and deprecated. You can, however configure RGeo factory application wide. For example in your initializer. RGeo::ActiveRecord::SpatialFactoryStore.instance.tap do |config| # By default, use the GEOS implementation for spatial columns. config.default = RGeo::Geos.factory_generator #...
Install Postgresql + Postgis on CentOS 7
Postgres provide postgres 9.3 and postgis 2.1 for Centos 7. Link here. Hopes it helps....
PostgreSQL: ST_GeomFromText(unknown) does not exist
sql,postgresql,function,geolocation,postgis
Point is not a PostGIS type, it's a native Postgresql type. I think you need to make your point column type geometry, then the PostGIS functions will work....
How to filter the max value and write to row?
sql,postgresql,postgis,greatest-n-per-group,groupwise-maximum
Answer to 1. To get the most common language and its count per Polygon, you could use a simple DISTINCT ON query: SELECT DISTINCT ON (h.gid) h.gid AS polygon, count(c.geom) AS ct, c.language FROM hexagonslan h LEFT JOIN points_count c ON ST_Within(c.geom, h.geom) GROUP BY h.gid, c.language ORDER BY h.gid,...
Successful CartoDB Python API call doesn't update map?
python,mapping,postgis,cartography
as John Barça says, in order to properly update your map in the client-side with the new data you'll need to use the CartoDB.js API. ps: I'm adding the comment as an answer in order to be able to mark it as valid....
how to install opengeo-postgis-shapeloader in ubuntu
I think you can follow this http://suite.opengeo.org/4.1/installation/ubuntu/install.html to install the mentioned packages
Openlayers-3 WFS-T (Post feature to postgis via geoserver)
postgis,openlayers-3,geoserver,fiware
I figured it out, because the name of the column was geometry i had to set attribute with same name.So what i did is: draw.on('drawend', function(evt) { var feature = evt.feature; feature.set('geometry', feature.getGeometry()); var fid = feature.getId(); var node = format.writeTransaction([feature], null, null, { gmlOptions: {srsName: "EPSG:3857"}, featureNS: "fiware", featureType:...
Effect of Distnace on PostGIS range queries
In short, yes. Spatial indexes in Postgres/Postgis are based on R-trees, which is a tree structure that attempts to subdivide your area based on bounding boxes, see http://en.wikipedia.org/wiki/R-tree, while trying to maintain a balance between speed of search and speed of insertion/deletion of new values. So, if you were lucky...
How to add distance from point as an annotation in GeoDjango
django,django-models,postgis,geodjango
I couldn't find any baked in way of doing this, so in the end I just created my own Aggregation class: This only works with post_gis, but making one for another geo db shouldn't be too tricky. from django.db.models import Aggregate, FloatField from django.db.models.sql.aggregates import Aggregate as SQLAggregate class Dist(Aggregate):...
how to check if two points are on the same line in postgis
postgresql,postgis,intersection
If the two points (or any number of points) are in a Multipoint you can use ST_Contains(Linestring, Multipoint) which will return true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior...
Lat-Long of exits on a Freeway e.g US 101 say arranged in N-S direction
You can run an Overpass query that gives you all ways tagged as highway=motorway_link that are connected to ways tagged [ref="US 101"]: [timeout:60][bbox:19.56,-132.63,51.62,-56.60]; way[highway][ref="US 101"]; node(w); way(bn)[highway=motorway_link]; out center; You can then export the data as GeoJSON....
Cannot get postgresql-9.3-postgis-2.1 on Ubuntu 14.04.1 Docker container
postgresql,docker,ubuntu-14.04,postgis,dockerfile
Thanks for everyone who tried to help me! Though I finally fix this myself, there is nothing wrong with the Dockerfile which is pretty simple, but the image I chose is not a typical Ubuntu image, the docker office use buildpack-deps:jessie instead of ubuntu:14.04 image: https://github.com/docker-library/python/blob/master/2.7/Dockerfile It caused different behavior...
Django and Postgis: How can I create a spatial table with CONSTRAINT enforce_geotype_geom in Django?
django,postgresql,postgis,psql
The old PostGIS model before 2.x used constraints like in the question. The newer style uses typmods like geometry(Point, 4326). The old style can set up manually using the appropriate DDL, or with the utility function AddGeometryColumn with use_typmod=false. The newer typmod style is typically recommended over the older constraints-based...
Show virtual field in rails view
ruby-on-rails-4,postgis,postgresql-9.2,ruby-on-rails-4.1
The complex structure was replaced by a database view. More details of how to do this can be found at this tutorial (http://pivotallabs.com/database-views-performance-rails/) and in one of my other SO questions here (Connecting database view with polymorphic model in rails)....
ActiveRecord Subquery Inner Join
ruby,activerecord,postgis,arel,rgeo
Disclamer: I've never used PostGIS First in your final request, it seems like you've missed the WHERE "users"."active" = 1; part. Here is what I'd do: First add a active scope on user (for reusability) scope :active, -> { User.where(active: 1) } Then for the actual query, You can have...
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
You don't have to read all of the columns of the table. Just read the ones you need. You can: MyObject.select(:id, :col1, :col2).where(...) ... and the omitted columns are not read. If you try to use a method that needs one of the columns you've omitted then you'll get an...
ERROR using ST_AsGeoJSON with PostGIS
The problem is that due to the restore you now have ambiguous functions called "ST_AsGeoJSON". You can run the following query to check: select * from pg_proc where proname = 'st_asgeojson' In a clean install, that should return you five rows. Bet you get more... The solution is indeed to...
Can I store circle and polygon within the same spatially indexed column in postgis?
geospatial,postgis,postgresql-9.3,spatial-query,spatial-index
There is no Postgis type for representing a circle with 100% accuracy, ie, with a centre point and a radius, as there is with SQL Server's circular arcs. As pointed out in the comments, you can approximate a circle with the 2nd form of ST_Buffer, ie, ST_Buffer(point, distance, num_seg_quarter_circle). As...
ST_SnapToGrid units
sql,database,postgresql,geocoding,postgis
The size is specified in the same units as the SRID of the geometry. In the case of SRID 4326 this is decimal degrees. The actual distance that is of course varies depending on the actual latitude and longitude of the point in question. If this matters, if might help...
Retrieve all latitude longitude points within x distance given an area
location,gis,latitude-longitude,postgis,geo
You can actually do this as a single SQL query in Postgis, namely: SELECT ST_AsText(ST_Transform(ST_SetSrid(ST_MakePoint( 500000 + x * 4828.03, 155000 + y * 4828.03), 27700), 4326)) FROM generate_series(0, 12) x, generate_series(0, 10) y; Some explanation is in order: Use Postgres's very useful generate_series function to create a grid in...
more than one row returned by a subquery used as an expression in postgresql
When using a subquery in a select statement, it must only return a single record. Perhaps you're looking for something like this instead: SELECT ST_AsText( ST_Intersection( ST_GeomFromText('LINESTRING(100 100, 200 200, 500 400)'), my_linestrings ) ) FROM test1dd Depending on the data type of my_linestrings, you may need to use ST_GeomFromText...
Get all points from a ShapeFile (.shp) in Java [closed]
Pretty bad question my friend. Try to provide SSCCE. If that binary you posted is really an .shp (file with the associated "helper" files such as .shx, .prj, etc), you can use a library such as GeoTools to read the file. Attempting to read the file "by hand" with RandomAccessFile...
Getting Geometry shape of the entire OSM database
Partitioning a database task into multiple PostgreSQL databases is usually the wrong approach. While this approach might be good one with other database software, it is not with PostreSQL. This is because it is a hassle to do cross-database queries, as you may need to do for this type of...
Display Data on Leaflet exclusively with Data from PostGIS
The linked code has this segment: L.tileLayer('https://{s}.tiles.mapbox.com/v3/{id}/{z}/{x}/{y}.png', { maxZoom: 18, attribution: 'Map data © <a href=\http://openstreetmap.org">OpenStreetMap</a> contributors, ' + '<a href="http://creativecommons.org/licenses/by-sa/2.0/">CC-BY-SA</a>, ' + 'Imagery © <a href="http://mapbox.com">Mapbox</a>', id: 'examples.map-20v6611k' }).addTo(map); This is the tile layer from Mapbox (the first line makes that pretty clear, with the url and the tileLayer...
How can I get center of three 3Dpoint in potgreSQl?
PostGIS can't do this (see this enhancement request). The best PostGIS can do is only a 2D centroid (via ST_Centroid(geom)): SELECT ST_AsText(ST_Centroid('MULTIPOINT Z(110 120 10,150 90 8,165 95 11)')); st_astext ------------------------------------------ POINT(141.666666666667 101.666666666667) However, the algorithm for a centroid for points is fairly straightforward. Just average the coordinate values for...
PLPGSQL Function to Calculate Bearing
sql,gis,postgis,plpgsql,postgresql-9.1
In PL/pgSQL it's most effective to do as much as is elegantly possible in basic SQL queries at once. You can largely simplify. I didn't get a definition of the sort order out of your question and left ??? to fill in for you: CREATE OR REPLACE FUNCTION get_bearings_from_points(_bgeom geometry)...
Tilestache - Mismatch between the coordinates present in the PostgreSQL/PostGIS and those returned by PostGeoJSON provider
postgresql,postgis,geojson,tilestache
Inserting the point with SRID - 4326 fixed the issue. This is the insert - INSERT INTO location(loc_id, loc_name, geo2) VALUES (3, 'Manchester, NH', ST_Transform(ST_GeomFromText('POINT(-71.46259 42.99019)',4326), 900913)); ...
NameError: uninitialized constant ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::TableDefinition
ruby-on-rails,postgis,ruby-on-rails-4.2
Apparently, the activerecord-postgis-adapter gem is not yet compatible with ActiveRecord 4.2. Either use the standard pg gem (that is supported natively by ActiveRecord), or downgrade to 4.1....
geodjango foreignkey by distance
You would need something like this: Show.representation_set.distance(user.geoloc, field_name='venue__coords') \ .order_by('distance') Unfortunately reversed one to one or one to many relationships are not supported with GeoQuerySet methods (including distance). I already created a feature request ticket for this. So for now I think you're stuck with: some hack in Python, not...
ST_GeomFromText incomplete output
pgAdmin limits the length of each column. This limit can be changed in the Query Tool options. File -> Options -> Query Tool -> Max. Characters per column http://www.pgadmin.org/docs/1.20/options-query_tool.html...
PL/pgSQL trigger to stop a river crossing another river
sql,postgresql,postgis,plpgsql,spatial
You are using a column of the to-be-inserted/-updated row (NEW.the_geom) as a temporary variable. So you will either over-write that column variable (giving the new row a bogus value), or get an irrelevant result on your IF check (because NEW.the_geom had data in it anyway before the trigger was run)....
Postgres split LineStrings and convert
postgresql,split,postgis,plpgsql
Use ST_DumpPoints like this: SELECT ST_X(d.geom), ST_Y(d.geom) FROM ST_DumpPoints( 'Linestring(3.584731 60.739211,3.590472 60.738030,3.592740 60.736220)') AS d; st_x | st_y ----------+----------- 3.584731 | 60.739211 3.590472 | 60.73803 3.59274 | 60.73622 (3 rows) ...
migrating from postgresql 9.3 to 9.4, postgis
linux,postgresql,postgis,archlinux
The symbolic link is pointing to the "share" files, used for CREATE EXTENSION and family. What you need is to point the .so files inside the directory returned by pg_config --pkglibdir: $ rm /opt/pgsql-9.3/lib/postgis-2.1 # it is a wrong link, so undo it $ ln -s /usr/lib/postgresql/postgis-2.1.so /opt/pgsql-9.3/lib/ Now the...
How to use ST_GeomFromText('Point($1 $2)', 4326) inside pg_query_params call
Use ST_MakePoint, which takes numeric inputs and can be used for parametrized queries. $query should look like this: INSERT INTO userloc (email, latitude, longitude, the_geom) VALUES($1, $2, $3, ST_SetSRID(ST_MakePoint($3, $2), 4326)); ...
GeoDjango dwithin errors when using django.contrib.gis.measure.D
python,django,postgresql,postgis,geodjango
I got a response to the ticket I submitted (https://code.djangoproject.com/ticket/22830). Apparently, I found a seemingly undocumented (or at least not clearly documented) problem that dwithin queries have with Distance objects. A dev says this: As your objects are in geographic coordinates (geometry fields default to WGS84), you have to provide...
Postgres preferring costly ST_Intersects() over cheap index
sql,postgresql,openstreetmap,postgis
maybe try something like this..? WITH wy AS ( SELECT * FROM planet_20141222.ways WHERE ((tags->'highway') IS NOT null) AND (lower(tags->'ref') LIKE lower('A8')) ) SELECT DISTINCT wy.id FROM wy, planet_20141222.polygons py WHERE py.id=51477 AND ST_Intersects(py.geom,wy.linestring); ...
Does phusion passenger use forking, and if so, where to set after_fork config?
ruby-on-rails,heroku,passenger,postgis
Yes, Phusion Passenger does forking, almost exactly like how Unicorn does it. Unicorn's default behavior (preload_app off) is the same as Phusion Passenger's "direct spawning" (not default in Phusion Passenger). Unicorn's preload_app on is the same as Phusion Passenger's "smart spawning" (default in Phusion Passenger). Sources: passenger_spawn_method documentation Appendix C:...
iterate and write separately linestrings (generate_series)
sql,postgresql,postgis,plpgsql
You don't need a custom function to do what you want. For instance, try just accessing the two members of ST_Dump (path and geom): SELECT id, name, (ST_Dump(ST_Boundary(the_geom))).path[1], ST_AsText((ST_Dump(ST_Boundary(the_geom))).geom) FROM poly_and_multipoly; id | name | path | st_astext ----+------+------+------------------------------------------- 1 | A | | LINESTRING(7.7 3.8,7.7 5.8,9 5.8,7.7 3.8) 2...
django-admin-bootstrapped messing with DateField, TimeField and PointField
javascript,python,twitter-bootstrap,django-admin,postgis
Two problems were describ and sorry for the poor lisibility. So here some answers : 1 - The DateField and Timefield seems to be issued and this will be fix : Fix 168 (last update 3 days ago) 2 - My PointField show up correctly after adding this options to...
Postgres pgrouting2 Dijkstra shortest path returns edges that don't exist
postgresql,postgis,postgresql-9.3,pgrouting
What kind of values to you have for edge ids? pgRouting only supports 32 bit integer values, if your ids are larger then they will get silently truncated. This is a known problem.
Map a PostGIS geometry point field with Hibernate on Spring Boot
spring,hibernate,postgresql,spring-boot,postgis
Finally I discovered that my configuration is ok and might be Jackson that cannot manage Point data type correctly. So I customized its JSON serialization and deserialization: add these annotations to our coordinates field: @JsonSerialize(using = PointToJsonSerializer.class) @JsonDeserialize(using = JsonToPointDeserializer.class) create such serializer: import java.io.IOException; import com.fasterxml.jackson.core.JsonGenerator; import com.fasterxml.jackson.core.JsonProcessingException; import...
Not finding Oracle Connection when a PostGIS connection is defined
oracle,hibernate,postgresql,postgis,hibernate-spatial
As commented, it looked to me that Hibernate will default to a spatialDialect which will be the first it finds from the available, and in that case it was Oracle, despite whatever was specified in the persistence.xml. The first workaround that I found was to annotate in the entity that...
Why does PostgreSQL combine series in a wrong way?
postgresql,combinations,postgis,generate-series
It's a quirk of how multiple set-returning functions are executed when invoked in the SELECT-list. You expect the result to be the cross product of the two, but that's not how it works. It's actually the lowest common multiple of the rowcounts of the two. See: Why do these join...
What is wrong with this ST_CONTAINS statement PostGIS - Find point in polygon
It looks like you are trying to use ST_Contains on geography types, but that function only works on geometry types. If you are OK with the intersects spatial relation (see DE-9IM), then use ST_Intersects(geography, geography)....
Local nominatim openstreemap with Speed limits
openstreetmap,postgis,nominatim
Probably yes, if you modify Nominatim accordingly. That is, importing speedlimit information (the maxspeed tag) into your database and adding it to query results. But there is no trivial way for just activating this feature. Note that Nominatim is a geocoder and not made for querying all kinds of information...
Postgis - one circles contains another
postgresql,postgis,coordinate-systems
You have to assign a CRS via Srid to the gps data (I assume they are in WGS84, thus srid 4326), then cast them to geography to enable setting the buffer radiae in meters, and cast them back to geometry for the st_contains function to work Query: SELECT ST_Contains(bigc::geometry,smallc::geometry) as...
Postgis declare a polygon from a text string
In the GIS world, polygons are formed using LinearRings, which are closed LineStrings. To be closed, the start and end points must be identical. So with the GIS convention, a triangle has four points, a square has five points, etc. More here....
Where do geocoding API places get their data from?
It will be different depending on where you are in the world. In the UK, where I have most experience, addresses are maintained by the Post Office and the actual coordinates by the national mapping agency, which are either surveyed by GPS or inferred from geo-referenced aerial photography. In the...
NPGSQL: Relate Operation called with a LWGEOMCOLLECTION type
This is due to trying to call an intersects or contains type query on a Geometry Collection, ie, where you have some mixture of points, lines and polygons (possibly multi). There are, at least, a couple of possible fixes. The first one is simpler, but seems a bit hacky, which...
GIS: partition area based on equal population
You should consider the Shortest splitline algorithm, recommended for creating optimally compact voting districts. Here is a description of its results in solving gerrymandering.
BasicDataSource not finding postgresql or postgis jdbc drivers
java,spring,postgresql,maven,postgis
The problem is created by Eclipse and poor maven management on my end. I built the war manually and noticed that 2 different postgresql jars were being loaded into the war, an older version from Hibernate spatial and a newer version I was loading via maven (this can also be...
error “illegal option --t” occus when import OSM map into postgis [windows environment]
postgresql,openstreetmap,postgis
There are at least 2 mistakes in your command -hstore instead of --hstore and _S instead of -S . So: osm2pgsql -U postgres -d osm --hstore -s -S ./default.style ./xxxx.osm ...
How to select inside a FOR-Loop for further computations?
postgresql,for-loop,postgis,plpgsql
You are thinking procedurally, while for most cases a set-based approach is superior in relational databases. Like: INSERT INTO table_other (point, polygon, result) SELECT a.name, b.name, calculate(a.?, b.?) AS result -- undefined what to do FROM table_point a JOIN table_polygon b ON ST_Intersects(a.way, b.way) WHERE a.name = 'Berlin'; ...
How to merge adjactent polygons to 1 polygon and keep min/max data?
The only way I could figure out how to do this, was to create a table of unioned geometries in a CTE, use ST_Dump to produce individual polygons (ie, 1-2 and 3-4-5-6 in your question) and then select the max and min values of the data attributes from the original...
Use KML to generate geometry
postgresql,kml,postgis,rails-geocoder,rgeo
I finally did it geometry = GeoRuby::SimpleFeatures::MultiPolygon.new doc = kml =~ /\<kml / ? Nokogiri::XML(kml) : Nokogiri::XML.fragment(kml) doc.search('Polygon').each_with_index do |hpoly,i| poly = GeoRuby::SimpleFeatures::Geometry.from_kml(hpoly.to_s) end geometry.empty? ? nil : geometry The kml file is directly the uploaded file where I applied the open method. I've found a lot of inspiration coming...
dyld symbol not found osm2pgsql to TileMill
The error message says that your executable (osm2pgsql) isn't finding a reference to a shared library. Perhaps it's not finding the shared library itself. From a command prompt: 1) Check which shared libraries osm2pgsql needs: cd /usr/local/bin ldd osm2pgsql <= This will print a list of shared libraries, and the...
Distance calculations using Postgis
ruby-on-rails,ruby,postgresql,postgis
The axis order is important. If one assumes the input is (lat lng), (and I don't blame most people for thinking that way,) you will get unexpected results: SELECT ST_AsLatLonText('POINT(37.215396881103516 -93.295166015625)'); 86°42'17.402"S 142°47'4.571"W or La Gorce Mountains, Antarctica And swapping them around to (lng lat): SELECT ST_AsLatLonText('POINT(-93.295166015625 37.215396881103516)'); 37°12'55.429"N 93°17'42.598"W...
Django duplicate key on an empty table
python,django,postgresql,postgis,geodjango
Well, turns out I am a complete imbecile. My last edit made me think... huh duplicates within the batch. The file itself didn't contain any duplicates but this sneaky b*****d of a typo: for loc_objs in izip(*[locations] * batch_size): print 'blah' was helpfully creating duplicates for me. It SHOULD have...
Trying to create and echo array via pg_query_params
If you just need to know how many points fall into each county specified in an array, then you can do the following in a single call to the database: SELECT countyname, count(*) FROM counties JOIN ltg_data ON ST_contains(counties.the_geom, ltg_data.ltg_geom) WHERE countyname = ANY ($counties) AND time >= now() -...
JPA Create Parent/child relationship with a lot of children
postgresql,ejb,postgis,jpa-2.1,hibernate-spatial
you're inserting a parent with all the children. In that case the Hibernate JPA indeed can be slow, but there are a few tips to improve the performance - check the hibernate batch guide http://docs.jboss.org/hibernate/core/4.0/devguide/en-US/html/ch04.html - I've used the hibernate.jdbc.batch_size parameter (set to e.g. 50) Good luck Gabriel...
Displaying PostGIS Data with Leafletjs
javascript,postgresql,yii2,leaflet,postgis
You didn't set height container. Set in your css file: #map { height: 400px; } And set zoom. Like that: L.tileLayer('https://{s}.tiles.mapbox.com/v3/{id}/{z}/{x}/{y}.png', {zoom: 10, ... ...
Will turning PostgreSQL database into PostGIS spatialy aware project and using it to store all web data?
Will [installing PostGIS] make the database sub-optimal for storing everyday website stuff? No, not at all. It just adds a bunch of new data types, functions, and index classes. You can install PostGIS and notice no difference for other data....
PostGis invalid geometry
Based on your previous question, ea.region appears to be text that looks like: ((-79.4609576808001,43.9726680183837)) The parse error or your processed data is obvious if you look at the value of your attempt: 'POINT(-79.4609576808001 43.9726680183837)' which is invalid because it has single quotes on each side. This is the same as...
Composite GIN/GIST index for uuid, timestamp, and geometry
postgresql,indexing,postgis,k-means
Per John Barça's suggestion, I used ST_DWithin with a GIST index on my geometries and timestamps and reduced the runtime to less than 10ms for the same query posted above. The only tricky piece realizing I needed degrees instead of meters for the geometry calculation (geographies can use meters). This...
Size of data type Geography(Point, 4326) in PostGIS?
Have a look at ST_Mem_Size. This gives you the size of toast tables, as well, so is more suitable than pg_total_relation_size and other built in Postgres functions, which do not -- although this only applies to larger geometries. Returning to your question, for a point, SELECT ST_Mem_Size(ST_MakePoint(0, 0)); returns 32...
PostGIS Trigger Function — max stack depth reached
postgresql,triggers,stack-overflow,postgis
The problem here is that you have a BEFORE UPDATE trigger on a table that has a statement to update the same table: recursion. After a while there are so many triggers waiting for the EXECUTE statement to finish (each invoking the same trigger function) that the stack is filled...
activerecord_postgis_adapter: undefined method `point' for nil:NilClass
ruby-on-rails,ruby,postgis,geos,rgeo
I created a github issue with a less consise but more detailed explaination. RGeos depends on GEOS. This error is a result of RGeos not being able to load GEOS. To check if this is the issue: $ rails c > RGeo::Geos.supported? => false To check if you have GEOS...
Count number of points within certain distance ranges from another set of points
You can do both types of queries relatively easily. But an issue here is that you do not know which customers are associated with which store locations, which seems like an interesting thing to know. If you want that, use the PK and store_name of the locations_table in the query....
Psycopg ppygis select query
python-2.7,postgis,psycopg2,postgresql-9.3
You are doing nothing wrong. Following the same steps as the PPyGIS basic example, I get the same hex-encoded EWKB as shown in the question (010100...), which is normally expected. Perhaps this worked with an older version of PPyGIS / Psycopg? It doesn't today. The package does not appear to...
How to use ST_GeoHash and ST_MakePoint postgis functions in rails before save?
ruby-on-rails,ruby,postgresql,ruby-on-rails-4,postgis
To generate a point that you can save in a postgis enabled database, you need to generate that point with a factory. What you're doing here: self.geopoint = "ST_MakePoint(#{latitude}, #{longitude})")" #not working is just setting self.geopoint to a string and trying to save it. As your database geopoint field is...
NOT EXISTS doesn't work
No need for NOT EXISTS: 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' AND a.cntry_name <> b.cntry_name --GROUP BY b.cntry_name -- should work without GROUP BY ...
Rails: Postgis doesn't create normal columns
ruby-on-rails,postgresql,postgis
This post helped me out, what I should really do is rake db:migrate:reset.
Custom SQL for Geodjango on ForignKey
django,django-models,gis,postgis,geodjango
Since the geometry you're measuring distance to is on UserProfile, it makes sense to query for UserProfile objects and then handle each Item object they own. (The distance is the same for all items owned by a profile.) For example: all_profiles = UserProfile.objects.all() for profile in all_profiles.distance(p).order_by('distance'): for item in...
Django with mod_wsgi doesn't work with Postgis
django,apache,mod-wsgi,postgis,selinux
I just found where the problem was. It was coming from SELinux policy that was apparently denying the permission for httpd to access the page. I had in my /var/log/httpd/error_log the following line: [Mon May 12 14:04:34.876392 2014] [core:notice] [pid 20070] SELinux policy enabled; httpd running as context system_u:system_r:httpd_t:s0 Then...
Postgis latitude/longitude read out reversed
ruby-on-rails,ruby,postgresql,activerecord,postgis
In PostGIS coordinates are set in (X, Y) order, or (longitude, latitude) for geographic coordinate pairs. So you should do: UPDATE users SET geocoordinates = 'SRID=4326;POINT(' || -118.25 || ' ' || 34.197 || ')' WHERE id = 347708 and then probably too: irb(main):058:0> b.geocoordinates = [-118.25, 34.197] Note that...
PostGIS ST_Contains failing to return data
It's not returning a match because the point is not inside that zone. Try this query: select st_xmax(area), st_ymax(area), st_xmin(area), st_ymin(area) from zones; You'll see that the point is well outside the the max and min points: "st_xmax","st_ymax","st_xmin","st_ymin" -118.303174,33.478098,-118.60403,33.299075 Your zone appears to be one island off the coast of...
Setting up Django with GeoDjango Support in AWS Beanstalk or EC2 Instance
amazon-ec2,postgis,elastic-beanstalk,gdal,geodjango
So I now have a working ebextensions workflow on 2013.09 stack ami ami-35792c5c. For the 2014.09 stack see the other solution. The solution below works with postgis by installing the needed gdal component, 00_repo.config needs to look like this: files: "/etc/yum.repos.d/pgdg-93-redhat.repo": mode: "000644" owner: root group: root content: | [pgdg93]...
postgis distance between two points on a line
SELECT ST_Length(ST_GeographyFromText(ST_AsText(ST_Line_Substring(line,ST_Line_Locate_Point(line,ST_ClosestPoint(line,fpt)),ST_Line_Locate_Point(line,ST_ClosestPoint(line,tpt)))))) As length_m, ST_Distance(ST_ClosestPoint(line,tpt)::geography, tpt::geography) as to_point_to_line_m, ST_Distance(ST_ClosestPoint(line,fpt)::geography, fpt::geography) as from_point_to_line_m, ST_AsText(ST_ClosestPoint(line,tpt)) as to_point_on_line, ST_AsText(ST_ClosestPoint(line,fpt)) as from_point_on_line, ST_AsText(tpt) as to_point,...
Converting Geometry to long/lat
java,eclipse,postgresql,postgis
The format is the Extended Well-Known Binary (EWKB), which is what PostGIS uses for storing geometry data in PostgreSQL. The ST_AsText() function turns this into the Well-Known Text (WKT) format. The ST_AsText() function needs a single column with a geometry data type. So change your code as follows: rs =...
Geometry datatype in PostgreSQL
CREATE EXTENSION adminpack; CREATE EXTENSION postgis; This installs postgis in the current database, with all data types and operators and the adminpack extension that it needs (adminpack is already on your system, most likely). Note that you have to do this for every database that you create, unless you create...
Unexpected result from CASE referencing another expression
It doesn't become completely clear from the question (yet), but my educated guess is you want this: SELECT st_Area(polygon) AS area -- or pick some other name! , CASE WHEN st_Area(polygon) > 100000 THEN st_area(st_buffer(polygon,500)) ELSE st_area(st_buffer(polygon,100)) END AS st_area FROM polygons LIMIT 10; You cannot reference the column alias...
Fluent NHibernate automap PostGIS geometry type
c#,nhibernate,fluent-nhibernate,postgis
I was finally able to resolve this by defining a custom UserTypeConvention, i.e.: using NetTopologySuite.Geometries; using NHibernate.Spatial.Type; public class PostGisPolygonUserTypeConvention : UserTypeConvention<PostGisGeometryType> { public override void Accept(IAcceptanceCriteria<IPropertyInspector> criteria) { criteria.Expect(c => c.Type == typeof(Polygon)); } public override void Apply(IPropertyInstance instance) { // Have to set CustomType to be able to...
How to find all (zip code, city name) pair through OpenStreetMap?
gis,openstreetmap,postgis,overpass-api
That kind of data structure you are lloking for is the basis for every so called 'geocoder' as nominatim. I recommend to look over there: https://wiki.openstreetmap.org/wiki/Nominatim https://wiki.openstreetmap.org/wiki/Nominatim/Development_overview
Is it possible to select the polygon with the maximum area with a query in GeoDjango or PostGIS?
I am not sure if you can call ST_Area directly in GeoDjango, in conjunction with an aggregate query, but you could use a raw SQL query. For example, using the correct Postgres query, as posted by @MikeT, top_area = ModelName.objects.raw('SELECT * FROM sometable ORDER BY ST_Area(geom) DESC LIMIT 1')[0] ...
Polygon insertion issue (due to SRID) on Postgres
postgresql,geolocation,geospatial,postgis,sequelize.js
Two things: You can add a geometry column directly in PostGIS 2.0+ just using the ALTER TABLE command. ALTER TABLE foo ADD COLUMN geom Geometry(Polygon,4326) As the error says, the geometry you're creating from JSON lacks the SRID of the column. So you need to ensure it is set. The...
MultiPolygon column by migration
ruby-on-rails,postgresql,migration,polygon,postgis
Finally I had to remove then recreate my column: remove_column :messages, :area_shape add_column :messages, :area_shape, :multi_polygon, srid: 3785 I think I can now understand why it's not possible. Indeed, it seems difficult to change a polygon type to a multi_polygon type without losing data logique. If you really need to...
SRID in CreateTopology () in postgis topology
postgresql,postgis,topology,srid
An SRID is an identifier that says what a particular set of coordinates means. If you have coordinates of, say, "45,50", is that latitude and longitude, meters north and south of some reference point or what? The SRID tells the system how to interpret those coordinates, and allows coordinate systems...
Using psql options in npgsql
vb.net,postgresql,postgis,psql,npgsql
Npgsql is an simply a driver for interacting with the database, HTML formatting of results is completely out of its scope (unlike psql, which is a user-facing console app). Note that there wouldn't be any "universal" way to format the HTML in a way that would satisfy everyone. However, it...
Why is there distortion for the same two geoJSON features with different base maps?
javascript,postgis,geojson,map-projections
The GeoJON shown on GitHub is projected using a Spherical Mercator projection. This done automatically is so it can overlay with other web-mapped data (e.g. I can see it is in Colorado). The second image is a Cartesian projection of raw coordinate data, so it is not projected. So the...