postgis

How expensive is ST_GeomFromText

In postgis, is the "ST_GeomFromText" call very expensive? I ask mostly because I have a frequently called query that attempts to find the point that is nearest another point that matches some criteria, and which is also within a certain distance of that other point, and the way I currently wrote it, it's doing the same ST_GeomFromText t...

good postgresql client for windows?

coming back to postgresql after several years of oracle ... what are the state-of-the art postgresql administrative/ddl generating/data inserting frontends on windows? it would be nice if it had integration for postgis as well. it should be standalone or alternatively a plugin for intellij idea thinking back, all the windows programs ...

Real (Great Circle) distance in PostGIS with lat/long SRID?

I'm using a lat/long SRID in my PostGIS database (-4326). I would like to find the nearest points to a given point in an efficient manner. I tried doing an ORDER BY ST_Distance(point, ST_GeomFromText(?,-4326)) which gives me ok results in the lower 48 states, but up in Alaska it gives me garbage. Is there a way to do real distan...

Why can't I do a "upper()" in my PostgreSQL database?

I created a database in PostgreSQL with "encoding = 'UTF8'", and loaded some UTF8 data in it. Selecting works fine, but when I try to do a "WHERE UPPER(name) = 'FOO'" in a query, I get an error ERROR: invalid multibyte character for locale My research seems to indicate that this is because the PostgreSQL installation was "initdb"-ed...

3 dimension GiS data with django and postgis

I could use a third dimension in my current project, which strongly involves google-maps and django. How do I deal with this? class Waypoint(models.Model): latlng = models.PointField() >>> p = Point( 5, 10, 20) <Point object at Ox...> >>> p.kml '<Point><coordinates>5.0,10.0,20.0</coordinates></Point>' >>> w = Waypoint.objects.all(...

Any good spatial database tutorials out there?

I was looking to explore the spatial capabilities of PostGIS or SQL Server 2008. Maybe Oracle as well. I was wondering if there are any good tutorials out there exploring the spatial capabilities of these database in particular or of this type of capability in general. Does anyone know of any? Book recommendations would work as well. ...

postgresql/postgis backup strategy to restore after geos/postgis recompile?

Current situation: Ubuntu 8.04 server edition (live server) Postgresql 8.3.7 (from standard repositories) Postgis 1.3.3 (from standard repositories) GEOS 2.3.4 (from standard repositories) Problem: GEOS contains bugs which are fixed in the 3.0 release. I have encountered these and need to upgrade GEOS/Postgis to include the GEOS...

Making PostgreSQL a little more error tolerant?

This is sort of a general question that has come up in several contexts, the example below is representative but not exhaustive. I am interested in any ways of learning to work with Postgres on imperfect (but close enough) data sources. The specific case -- I am using Postgres with PostGIS for working with government data published in...

How do you select Arcs inside a boundary in PostGIS?

I'm searching an answer for this question and all I found on http://postgis.refractions.net/documentation/manual-1.3/ch04.html#id2572194 is SELECT road_id, road_name FROM roads WHERE roads_geom && GeomFromText('POLYGON((...))',-1); should I put the boundary vertexes in this SQL replacing the '...' ? ...

MySQL - Geo-Spatial Object.

I've been working in Postgis for point in poly's and i have this function: select * from table_name where st_contains(column, st_setsrid(st_makepoint(-92.095109, 46.804100),4326)); the column is a multipoly(((points))) I'm trying to find a way to do a similar exercise with MySQL, and wondered if there were similar functions to do so. ...

SQL query for point-in-polygon using Postgres

I have the following simple table: CREATE TABLE tbl_test ( id serial NOT NULL, poly polygon NOT NULL ) WITH (OIDS=FALSE); I then try to insert a row with a polygon: insert into tbl_test values(1, PolyFromText('POLYGON((0 0, 10 10, 10 0, 0 0))')) And run into this error: column "poly" is of type polygon but expression is of ...

PostGIS geometry support

Can postgis for postgresql support complex geometric types like spheroids? ...

Querying a PostGIS database with a KML "shapefile"

I am working on a Google Maps application that doee the following (on a low zoom level): Prints a bunch of polygon overlays onto the map. These polygons are created from KML files that I created with Google Earth. Basically these polygons cover continent sized chunks of the earth. 15 of them cover the entire land of the world. On top o...

How can I create a polygon using fields in PostgreSQL?

I have 8 real values in a table that I'd like to combine into a polygon. I haven't been able to figure out how to create a polygon using these values though. I keep trying variations of SELECT polygon(lat1,lon1,lat2,lon2,lat3,lon3,lat4,lon4) FROM table; but keep getting errors about the polygon function not existing or an invalid inpu...

Why is PostgreSQL so slow on Windows?

We had an applicationg running using MySql. We found MySql was not suitable for our app after we found that it didnt support some of the GIS capability that PostGIS has (note: mysql only supports minimum-bounding rectangle GIS search). So we changed our DB to PostgreSQL. We then found out that Postgresql 8.2 running on Windows is so muc...

Convert Postgis table to SQL Server 2008...

Is there a simple way to do that? ...

Problems with $libdir on PostgreSQL

In short, my question is "why doesn't $libdir work on my PSQL installation." CREATE FUNCTION st_box2d_in(cstring) RETURNS box2d AS '$libdir/liblwgeom', 'BOX2DFLOAT4_in' LANGUAGE c IMMUTABLE STRICT; yields an error could not access file "$libdir/liblwgeom": No such file or directory while CREATE FUNCTION st_box2d_in(cstri...

Encoding problems with ogr2ogr and Postgis/PostgreSQL database

Hi! In our organization, we handle GIS content in different file formats. I need to put these files into a PostGIS database, and that is done using ogr2ogr. The problem is, that the database is UTF8 encoded, and the files might have a different encoding. I found descriptions of how I can specify the encoding by adding an options param...

No liblwgeom when using postgresql84 and postgis on Snow Leopard with MacPorts

I'm trying to get my dev environment back up and running after upgrading to Snow Leopard. In particular, I need postgresql and postgis working, but I'm running into the following problem. After installing both with the following: sudo port install postgresql84 postgresql84-server postgis I get errors like the following when I try to...

Drawing a line in PostGIS using Nearest Neighbour Method

This is a cross post from an email I sent to the PostGIS mailing list So far in my endeavor to create a line between a point and its projected location on a line has been long but I'm almost there. As of yesterday, and before including any nearest neighbor analysis, I got the results shown in this image: As you can see, each point in...