Tag Info

Hot answers tagged

13

Because your data isn't projected - it is points on a spheroid - linear distances sort of don't make sense. Five miles at the equator is a much smaller angle than 5 miles on the arctic circle. But luckily PostGIS (>= 1.5) has the answer you're looking for: SELECT * FROM table WHERE ST_DWithin(ST_GeogFromText('SRID=4326;POINT(0,0)'), geography(latlon), ...


11

CREATE TABLE new_road_cl AS SELECT ST_Intersection(r.geom, b.geom) AS geom, b.attr1, b.attr2, b.attr3, r.attr1, r.attr2, r.attr3 FROM road_blocks b, road_cl r WHERE ST_Intersects(r.geom, b.geom);


8

Right-click on any database, choose New Database, fill Name and Owner and, under Template, choose template_postgis. Let me know if that works?


8

You must use ST_X function. Assuming that you want to fill a column of a table that already has a geometry column with the x coordinate of the centroid you can use something like: UPDATE test_table SET x = ST_X(ST_Centroid(the_geom)); If you need something different post the complete structure of the table if you need more accurate help.


8

Apart from, as @underdark says ST_Length() returns a cartesian distance between the two points which in an unprojected CS is meaningless in this case, I would go with the answer you get from PostGIS. Google Earth uses a spherical globe, whereas the WGS84 globe is a spheroid - it is slightly squashed at the poles. Over short distances, there won't be much ...


8

I would recommend using pg_dump and pg_restore which work very well in exporting data from one database and then restoring it to another database. there is a nice tutorial here http://www.mkyong.com/database/backup-restore-database-in-postgresql-pg_dumppg_restore/ But simply you will want to export using a command like pg_dump -U username databasename ...


7

Postgresql is quite good handling multi-cores, especially since every connection gets spawned to a new process and thereby gets handled by the OS process scheduler. I've run large postgresql databases on both windows and linux (ubuntu server) and they both perform very well. However, most likely your performance will be heavily dependent on how well your ...


7

Try this: SELECT ST_Transform(geometry( ST_Buffer(geography( ST_Transform( point, 4326 )), 600000)), 900913) FROM points` This flips into geography then uses the inbuilt SRID selection to (ironically) flip back into geometry where a good planar buffer is run then flips back. The trouble with ...


7

Fist about joins and relates. The former term is to "join" one table to another using some criteria, such as data common to the two tables. The later term is more a GIS term for how one spatial data relates to another, e.g. see ST_Relate and DE-9IM. One might join a table to another using a relate. So for your situation, "how to join non-spatial table to ...


6

Revert your software to 1.5, then follow the HARD UPGRADE instructions in the manual, doing the dump step, then the software upgrade, then the restore steps. http://postgis.net/docs/manual-1.5/ch02.html#hard_upgrade


6

Two options come to mind. If you want a specific LINESTRING then you can use ST_NumGeometries() and ST_GeometryN(). Alternatively, if you want all the sub-geometries, ST_Dump() is the way to go. After actually reading the question, you will need to do something similar to this post from the postgis-users list: SELECT ST_AsText( ST_MakeLine(sp,ep) ) FROM ...


6

Maybe the user aus is not the owner of the database: first become super user: $ sudo su change to postgres user: $ su postgres access postgres, and change the owner, also grant permissions $ psql ALTER DATABASE aus OWNER TO aus; GRANT ALL PRIVILEGES ON DATABASE aus TO aus; then maybe you still need to change the owner of the tables one by one, I ...


6

The order of latitude and longitude has been reversed in the call self.location = Point(self.latitude, self.longitude) That is because Points expect the x-coordinate (longitude) to be the first argument. Indeed, the distance between points at latitude -74 degrees and longitudes at 4.6 and 11.0 degrees is approximately 206 kilometers.


6

You can manually register Geometry Columns in geometry_columns. Something like: INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, "type") VALUES ('', 'public', 'residentialBuildings', 'the_geom', 2, <yourSRID>, 'MULTIPOLYGON')


6

To create 1 random point for each polgyon in a table I have used this table and code: DO $$ DECLARE r RECORD; BEGIN FOR r IN SELECT id_0 FROM "Grid" LOOP -- RAISE NOTICE 'affected row id: %', r.id_0; UPDATE "Grid" SET "point_geom" = (SELECT RandomPointsInPolygon(geom, 1) FROM "Grid" WHERE "id_0" = r.id_0) WHERE "id_0" = r.id_0; END LOOP; END$$; ...


5

The result is a spatial table with only one column in geometry form. The binary representation is correct. If you are using this outside of PostGIS (say ArcGIS or QGIS or whatever), it is probably looking for two things: An entry in the geometry_columns. Solve it by adding an entry manually. A unique id. Solve it, by creating a sequence and make it the ...


5

If you want x and y columns: ALTER TABLE "test_table" ADD x double precision; ALTER TABLE "test_table" ADD y double precision; UPDATE "test_table" SET x = ST_X(ST_Centroid(the_geom)); UPDATE "test_table" SET y = ST_Y(ST_Centroid(the_geom)); If you rather want a geometry column (adjust schema name and CRS to fit your needs): SELECT AddGeometryColumn ...


5

If PGAdmin is definitely succeeding at operations involving the geography type, then I can only suggest making sure that psql and PgAdmin are actually connected to the same database. The error reported by psql is from the server, psql is just the messenger. are you specifying a spatially-enabled database when you invoke psql? If not, see ...


4

To select all the points within a bounding box use the ST_Within function of PostGIS and create a bounding box with ST_MakeEnvelope: SELECT * FROM geom_data WHERE ST_WITHIN(point, ST_MakeEnvelope(-122, 37, -121, 38, 4326)); In your case it is also possible to use the @ operator, that makes use of the geometry indexes (and thus it is faster), but only ...


4

OKay, here in an answer, some R code - uses rgeos, sp: For p = a single SpatialPolygon or single row of a SpatialPolygonsDataFrame: slice <- function(p,n=20){ bb = bbox(p) ys = seq(bb[2,1],bb[2,2],len=n) ll = list() for(s in 1:(n-1)){ ll[[s]] = Polygons(list( Polygon(cbind( ...


4

I am a maintainer at Application:Geo. I will restore postgis 1.5.3 binaries in the next few hours. If you are in a hurry check http://download.opensuse.org/repositories/home:/tzotsos:/postgis/openSUSE_12.1/ where the old binary is posted now. Thanks for the report, Angelos


4

You can increase the quad_seg value to get a more accurate circle. To get a real circle you have to work with a curved geometry type, but I don't know what software can show that. The accuracy of srid 90013 is also very bad since it is a projection covering the whole world. You will get a more accurate result with a local projection.


4

perhaps you want the ST_DWithin function instead. see note in the st_buffer doc. ST_Buffer People often make the mistake of using this function to try to do radius searches. Creating a buffer to to a radius search is slow and pointless. Use ST_DWithin instead.


4

I used an approach with voronoi polygons once. I did it by and hand I only have a vague idea of how you could do it with shapely, but here's how it goes. First, you extract the vertices of each overlapping polygons and create Voronoi polygons from them (vector > geometry tools > extract nodes & vector > geometry tools > vononoi). With the resulting ...


4

try to use following sql. ALTER TABLE network ADD COLUMN shape_leng double precision; UPDATE network SET shape_leng = length(the_geom); i hope it helps you...


4

Forget about pgRouting. There are lots of other things you should check. Check if your LAN cable is plugged or WiFi is connected. Check if your PostgreSQL server is working properly. Check if your connection host/port to PostgreSQL is set correctly. Check if your machine or PostgreSQL server is ignoring port 5432.


4

If you want to make browser download the generated KML, an extra header is needed: header("Content-Disposition: attachment; filename=MY_KML_FILENAME.kml"); Otherwise, just ignore the message, it's harmless. Chrome doesn't recognize the KML-specific Content-Type, and expects ordinary text/xml or application/xml, but since it's all XML, it should be parsed ...


4

The ST_Transform function takes the desired coordinate system as the second parameter - see ST_Transform page for PostGis. untested example .. SELECT ST_AsText(ST_Transform(the_geom,4326)) As wgs_geom;


3

It depends where your circles are being created. Are they near the equator or closer to the poles? Take a look at this map. Do you think Antartica or Greenland are really that big? That is the projection you are using, right? I would recommend you do a quick read of this USGS document on projections, particularly the table below that gives you a quick idea ...


3

It is a Geometry (a Point in fact). What you are seeing is the text representation of the point as Well Known Binary (WKB) for debugging purposes you'd probably want well known text (WKT) (see http://postgis.refractions.net/docs/ch04.html#OpenGISWKBWKT), select ST_AsText(ST_Centroid(a.the_geom) ) FROM "test_table" AS a Will show what you want to ...



Only top voted, non community-wiki answers of a minimum length are eligible