update tableT pp set descript = (select replace(descript, '�', 'D') from tableT where id = pp.id) Why don't use replace?...
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,...
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 #...
Postgres provide postgres 9.3 and postgis 2.1 for Centos 7. Link here. Hopes it helps....
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....
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,...
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....
I think you can follow this http://suite.opengeo.org/4.1/installation/ubuntu/install.html to install the mentioned packages
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:...
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...
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):...
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...
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....
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,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...
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)....
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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)...
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)); ...
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....
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...
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...
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)....
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) ...
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...
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)); ...
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...
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); ...
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:...
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...
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...
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.
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...
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...
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...
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)....
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...
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...
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....
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...
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...
You should consider the Shortest splitline algorithm, recommended for creating optimally compact voting districts. Here is a description of its results in solving gerrymandering.
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...
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 ...
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'; ...
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...
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...
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...
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...
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...
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() -...
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...
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 [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....
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...
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...
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...
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...
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...
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....
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...
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...
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 ...
ruby-on-rails,postgresql,postgis
This post helped me out, what I should really do is rake db:migrate:reset.
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,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...
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...
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...
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]...
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,...
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 =...
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...
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...
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...
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
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] ...
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...
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...
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...
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...
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...