Hot answers tagged postgis-1.5
12
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), ...
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 ...
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 ...
5
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.
5
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 ...
5
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')
4
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 ...
4
you can increase the quad_seg value to get a more accurate circle. to get a real circle you have to work with curved geometry type but I don't know what software that can show that.
tha 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.
/Nicklas
4
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.
4
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
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
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
...
4
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 ...
3
There are two general reasons to avoid tiny coordinates (or tiny differences of coordinates). Because I cannot (readily) find any PostGIS documentation about its internal storage format for coordinates, I will give them both.
Integer representation
Many GISes convert coordinates into indexes into an extremely fine grid of points covering a study region or ...
3
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.
3
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 ...
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
According to the latest PostGIS docs on loading raster data:
The older version of this tool was a python script. The executable has replaced the python script. If you still find the need for the Python script Examples of the python one can be found at GDAL PostGIS Raster Driver Usage. Please note that the raster2pgsql python script may not work with ...
3
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 ...
2
Nathan,
1) Make sure your geometry is valid - invalid geometries are a major cause of these kind of errors.
SELECT ST_IsValid(geom);
2) Which version of geos are you running? Do a
SELECT postgis_full_version();
GEOS 3.2+ runs into fewer of these topological exception issues.
To Adam -
Paul decided one day he was too lazy to type the_geom :) -- so he ...
2
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 ...
2
You may need to edit /var/lib/pgsql/data/pg_hba.conf to add something like:
host all all 128.118.54.0/24 md5
so other machines on your network (128.118.54.*) could log on to the database (and remember to restart postmaster).
I have some notes on setting up PostGis and adding OSM data to it at ...
2
To select all 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's also possible to use the @ operator, that makes use of the geometry indexes (and thus it is faster), but only considers ...
2
For PostGIS 1.5, there is no way to be using the latest raster support in PostGIS. You will need to be on PostGIS 2.0 for the latest public release.
BUT for your needs, you can simply download the python script from raster/scripts/python/raster2pgsql.py from any recent source tarball. I think you'll need numpy though...
2
If the data in the network table is either being updated or added, it would be wise to make a trigger function to update the data, so the line lengths are always up-to-date. To do this, make a trigger procedure, written in PL/pgSQL:
CREATE OR REPLACE FUNCTION shape_leng() RETURNS trigger AS
$BODY$BEGIN
NEW.shape_leng := ST_Length(NEW.the_geom);
RETURN ...
1
Assuming that your table looks like
CREATE TABLE geom_data ( id serial );
-- i use 'point' for the name of the geometry column, but remember that
-- a common practice is call it 'geom' or 'the_geom'
SELECT addgeometrycolumn ('geom_data', 'point', 4326, 'POINT', 2);
INSERT INTO geom_data (point) (SELECT ST_POINTFROMTEXT('POINT(-122.161445617676 ...
1
i think you have to check your GEOS - (Geometry Engine - Open Source) version. Box Types and Geometries mostly make the problem with old version of GEOS Library...
you can learn your GEOS version from PostGIS with this way :
postgres=# select PostGIS_full_version();
postgis_full_version
...
1
You have two options inside of Postgis. Either use the ST_Force_3D function in your INSERT statement or create a trigger for that table.
I have not run these on an actual database, so there may be some syntax issues, but the general idea should be fine.
Insert statement:
INSERT INTO mytable (the_geom) VALUES(ST_Force_3D(ST_GeomFromText('POINT (0 0)'))
)
...
1
I think you'll just have to make sure the points are 3d on creation, whenever that may be.
If you have the 2d points in a list it's easy to convert them.
points_2d = [Point(1,1), ...]
# create new points with z value
points_3d = [Point(pnt.x, pnt.y, 0) for pnt in points_2d]
Only top voted, non community-wiki answers of a minimum length are eligible
