BostonGIS Blog

Essential PostgreSQL and new in PostGIS

nospam@example.com (Regina Obe) — Sun, 20 Sep 2009 23:05:00 -0400

Essential PostgreSQL Refcard is out

We've been working on writing our book on PostGIS PostGIS in Action. We have also been working on an Essential PostgreSQL DZone RefCardz to both provide free publicity for our upcoming book and also to hopefully serve a greater good to the PostgreSQL community. We are pleased to announce that the RefCard is finally done and out on the street. We still have a ways to go with the book (slaving on chapter 8), but we hope the RefCard will be a nice companion to our book. The RefCardz is available for free download at Essential PostgreSQL http://refcardz.dzone.com/refcardz/essential-postgresql?oid=hom12841.

If you've never looked at a DZone RefCard, we encourage you to. They are basically 6 pages of the most important (well at least what the author thought) need to know about a piece of software or technology. They also make attractive handouts at conferences. We did an Essential PostgreSQL because we felt there was a greater audience that could benefit from that card not just in the spatial community, but in the PostgreSQL community at large. We hope to do one later targeting PostGIS. It would be neat if similar cards could be done for other common Open source GIS tools such as Mapserver, Geoserver, and OpenLayers.

New on the PostGIS front

Paul has managed to sneak in Geography support in the upcoming PostGIS 1.5. There isn't much to see yet except ability to create geography using PostgreSQL typmod syntax, do bounding box queries, convert back and forth between geometry and geography. You can think of the new geography counterpart as native geodetic support similar to geography in SQL Server 2008. It will eventually have ST_Distance and ST_DWithin functionality and hopefully that will make it into PostGIS 1.5. We'll be providing experimental windows builds of this in the next week or so.

SQL Server 2008 R2 CTP

nospam@example.com (Regina Obe) — Sat, 15 Aug 2009 09:30:00 -0400

One of the interesting things to come out this week is the SQL Server 2008 R2 CTP

One of the enhancements we are really looking forward to and interested in experimenting with is the Report Builder 3.0/Reporting Services support for Geospatial data. We hope to try out the SQL Server 2008 R2 CTP 1 in the next 2 or so weeks to test drive these features and maybe provide some reporting tutorials on BostonGIS.

One thing I am interested in seeing is whether the geospatial support can be leveraged for other spatial databases besides SQL Server. One thing I have to say is nice about the SQL Server add-ons like SSIS, Reporting Services etc. is that they can be used for other databases such as Oracle and PostgreSQL. Reporting Services has native drivers for Oracle and I believe Terradata and some others. It would be nice to implement similar drivers for PostgreSQL/MySQL and other open source databases. For most OS dbs, you usually have to use the ODBC driver,which is okay but not great. Its strange you can't use the ADO.NET driver, it seems you need a special Reporting Services provider driver, or at least we haven't been able to. It is possible to use the report builder in a stand alone mode (client mode -- no reporting services) and then I believe you can use ADO.NET drivers. We haven't played around with that but plan to.

To be honest we don't really use reporting services that much, but lately we have been forced into it since a lot of our bigger clients are married to it mostly because of the subscription support capabilities that allows you to mail reports on a scheduled basis to groups of users. The report builder isn't too bad either with some training. Its also tied in with other Microsoft stack things like Microsoft CRM/SharePoint etc. that many of our clients use so is a big part of their workflow.

We also want to check out how the spatial support has changed/improved since the SQL Server 2008 RTM.

More Database comparisons

We also just updated our basic comparison between SQL Server 2008, PostgreSQL 8.4, and MySQL 5.1 . This is not the spatial part, just general database features. It seems to be garnering a lot of traffic.

For our spatial compare that we did a while back. SQL Server 2008, PostGIS, MySQL, we'll probably repeat that exercise again when SQL Server 2008 R2 and PostGIS (1.5 or 2.0) come out. For our next iteration, we'll probably leave out MySQL since MySQL spatial support doesn't appear to have changed much. But instead we will replace with Oracle Spatial/Locator (and possibly Db II). Since more people seemed interested in how Oracle compares with the other two than how MySQL compares.

Busy month for PostGIS

nospam@example.com (Regina Obe) — Mon, 03 Aug 2009 18:32:00 -0400

This has proved to be a busy past two weeks for PostGIS. Couple of happenings.

Presentations Galore

UPDATE: PgDay San Jose 2009

This was right before OSCON 2009 and sadly we missed it. Brian Hamlin gave intro talk on PostGIS and OSGEO in general and they have videos. What works with Postgres The Open Geo Data Interoperabilty Overview. Videos can be downloaded from http://media.postgresql.org/pgday-sjc-09/

OSCON 2009 in San Jose there were 2 PostGIS talks. At least two we are aware of.

We gave one on PostGIS spatial tips and tricks show-casing some of the nifty features in PostGIS 1.4 and PostgreSQL 8.4. You can access the slides here and data here
Chander Ganesan gave a talk on "Spatializing your Data with PostGIS, GeoDjango & OpenLayers". His slides are up as well.

The week after was GeoWeb 2009

with the OpenGeo gang of Paul Ramsey and Steven Citron-Pousty giving a workshop on the OpenGeo GeoStack stack with PostGIS being part of that stack.

PostGIS 1.4 is out

PostGIS 1.4 came out the week of OSCON. Finally we can move on to worrying about 1.5. If only ~~we could get GEOS 3.2 to compile under MingW~~ We can now compile GEOS 3.2 trunk under mingw so hope to be releasing windows PostGIS 1.5 experimental binaries very shortly. Lots of neat stuff already cooking in 1.5. We have new buffer logic and ST_HausdorffDistance and other side things going on like improving speed of distance functions and Paul tooling on geography and typmod lots of healthy discussions about typmod.

PostGIS is nearing incubation

Last but not least PostGIS is coming closer to being an OSGEO incubation project. Our defacto steering committee is feeling a bit more real.

Row Constructors in SQL Server 2008 and PostgreSQL - PostGIS and centroids

nospam@example.com (Regina Obe) — Sun, 24 May 2009 11:46:00 -0400

One thing we are really fond of are the ANSI SQL Standard Row Constructors. PostgreSQL has supported this feature since 8.2 I believe and SQL Server 2008 supports it too, but prior SQL Server's do not. MySQL has supported for as far back as I can remember.

Any rate we thought what a fun way to play around with row constructors in SQL Server 2008 than to write queries we would write in PostGIS in SQL Server 2008. The results were a bit surprising when testing out Centroid. The STPointOnSurface I can accept as okay since I think the point is only guaranteed to be on the surface and when you look at say IBM DB II specs and ESRI manual it suggests these should only work for POLYGONS and MULTIPOLYGONS. Both PostGIS and SQL Server 2008 seem to return point on surface for any common geometry and both are on the surface just a different point. For Centroid however SQL Server as far as we can tell doesn't work for points, multipoints, linestrings and I presume probably only works for POLYGONS and MULTIPOLYGONs. Checking the docs confirms that SQL Server does not return a centroid for anything but POLYGONS and MULTIPOLYGONS. This is a bit of a bummer. Well given that PointOnSurface always works and that is what a lot of people expect from Centroid I guess the lesson is to use STPoinOnSurface. Centroid is not guaranteed to return a point on the surface in any spatial database we know of especially when you have polygons with holes.

UPDATE: On further inspection it appears the MM specs (at least the version I have seen) only specify centroid and point on surface for surface geometries (polygons and multipolygons) and spatial databases seem a bit split as to how they extend the spec in this regard. PostGIS extends the concept to apply to all common geometries. SQL Server extends it for PointOnSurface but not for Centroid. IBM extends it for centroid but not PointOnSurface and Oracle appears to read the spec to the letter not extending for either.

Anyrate here are 2 queries which are in theory identical but not.

--Query on SQL Server 2008

SELECT name, the_geom.STCentroid().STAsText() As cent_geomwkt, 
            the_geom.STPointOnSurface().STAsText() As pos_geomwkt
FROM 
( VALUES ('mpoint', geometry::STGeomFromText('MULTIPOINT(-1 1, 0 0, 2 3)',0 ) ),
('mlinestring', geometry::STGeomFromText('MULTILINESTRING((0 0,0 1,1 1),(-1 1,-1 -1))',0) ),
('polygon', geometry::STGeomFromText('POLYGON((-0.25 -1.25,-0.25 1.25,2.5 1.25,2.5 -1.25,-0.25 -1.25), 
(2.25 0,1.25 1,1.25 -1,2.25 0),(1 -1,1 1,0 0,1 -1))', 0) )  ) As foo(name, the_geom);

--Results
name      cent_geomwkt    pos_geomwkt
point	NULL	POINT (-1 1)
mpoint	NULL	POINT (2 3)
mlinestring	NULL	POINT (0 0.5)
polygon	POINT (1.125 0)	POINT (1.1666666666666667 1.1666666666666667)

Same query on PostGIS :


SELECT name, ST_AsText(ST_Centroid(the_geom)) As cent_geomwkt, 
     ST_AsText(ST_PointOnSurface(the_geom)) As pos_geomwkt
FROM (VALUES ('point', ST_GeomFromText('POINT(-1 1)') ),
('mpoint', ST_GeomFromText('MULTIPOINT(-1 1, 0 0, 2 3)') ),
('mlinestring', ST_GeomFromText('MULTILINESTRING((0 0,0 1,1 1),(-1 1,-1 -1))') ),
('polygon', ST_GeomFromText('POLYGON((-0.25 -1.25,-0.25 1.25,2.5 1.25,2.5 -1.25,-0.25 -1.25), 
(2.25 0,1.25 1,1.25 -1,2.25 0),(1 -1,1 1,0 0,1 -1))') )  )
As foo(name, the_geom);

--Results
    name     |               cent_geomwkt                |   pos_geomwkt
-------------+-------------------------------------------+-----------------
 point       | POINT(-1 1)                               | POINT(-1 1)
 mpoint      | POINT(0.333333333333333 1.33333333333333) | POINT(-1 1)
 mlinestring | POINT(-0.375 0.375)                       | POINT(0 1)
 polygon     | POINT(1.125 0)                            | POINT(-0.125 0)

New Book PostGIS in Action

nospam@example.com (Regina Obe) — Sun, 10 May 2009 15:28:00 -0400

As many people may have heard and as Mateusz kindly already commented on; Leo and I already mentioned in our Postgres OnLine Journal site, we are writing a book specifically focused on everything PostGIS. The hard copy version is do out around January of 2010, but you can pre-buy now and view the draft chapters as we write them. We are currently working on chapter 4 and the helper appendices and will try to get out more content before the end of May. Of course the more people pre-buy the more encouraged we will be to write quickly. You can download the first chapter for free from Manning: PostGIS in Action

As Brent Wood kindly commented on our Author Blog -- where is the primer on SQL and writing functions? before he quickly observed we have it planned for the appendix. Originally we stupidly had that as part of the meat of the book, but as our editor, Sebastian, and Marjan Bace rightfully commented, such things would bore people to death who are familiar with these things. So we are stuffing these in the appendix where you can refer to them, as we start doing wacky things you have never seen before done or envisioned relational databases can do, and where they don't interrupt the flow of the book and frustrate people already intimate with databases who want to learn about spatial.

Our book has now reached pre-book sales and nicely we've already got some purchases. You too can buy a copy (whether just MEAP/eBook or MEAP/eBook/hardcopy, start reading, make comments and thus help guide the direction of the final book. We look forward to getting feedback from people and hope this will be a valuable resource for those using PostGIS and future PostGIS users as well as encouraging adoption of PostGIS.

Below is more or less a repeat of what we posted to the PostGIS newsgroups with some minor edits:

Continue reading "New Book PostGIS in Action"

PostGIS, PL/Pyton, Events, Mapserver XML Mapfiles

nospam@example.com (Regina Obe) — Fri, 20 Mar 2009 03:56:00 -0400

PostGIS 1.4 out soon

PostGIS 1.4 will be out soon, which will be good because it feels like forever we've had this release baking in the oven. The key changes are as follows: Which many are detailed in New in PostGIS 1.4

ST_IsValidReason() -- requires GEOS 3.1 -- will tell you why a geometry is not valid. Its a complement to ST_IsValid() which has existed for as long as I can remember
Improvements in all the aggregate functions ST_Collect, ST_Union, ST_Accum to use new array logic that can better handle collecting of many geometries. You won't notice this benefit much until you start collecting and unioning 1000s of geometries
Name change of hidden st*garray functions to be exposed as ST_Collect(geometry[]), ST_Union(geometry[]) ..etc
Cascade Aggregate Union -- requires GEOS 3.1 -- need I say more?
Prepared geometry - required GEOS 3.1
Better debugging facilities and cleaner code base

Sadly ST_DumpPoints did not make it into 1.4, and we seem to have jumped to 2.0 thinking. Where is 1.5? 2.0 seems offly ambitious (geodetic, better 3d, WKT Raster, restructure to allow support for more types, improved curve) that I feel we really need at least a 1.5 to cushion the path. There are a lot of things we have left on the plate such as ST_DumpPoints, Steve Frost's Tiger Geocoder upgrade for new tiger 2007/2008 format, minor enhancements to distance speed and functionality and dumper that don't require major restructure that I feel those should just come first and that need only be a 3-6 month incubation. Given our new policy of not introducing new functions in minor releases, it would seem almost necessary to have at least a 1.5

Continue reading "PostGIS, PL/Pyton, Events, Mapserver XML Mapfiles"

PostGIS on Windows 2008 Server in the Cloud

nospam@example.com (Regina Obe) — Mon, 12 Jan 2009 03:20:00 -0500

GoGrid

Well we knew this day would come when we would have to start experimenting with Clouds. Now it seems a lot more cloud providers are coming on line. For our first experimentation we chose GoGrid because they offer a free 1 month/50$ trial plus we needed to test out Windows 2008 Server as well as Linux and they had an offering for both where as Amazon seems to only offer Windows 2003 and its very new. We were hesitant to go the Windows 2008 Server 64-bit route since we aren't sure how well PHP works in 64-bit IIS 7 and in general I've had nightmares with IIS in 64-bit mode e.g. the fact that they will not be releasing 64-bit jet drivers and in many cases there is no alternative in the ADO.Net world and so forth.

The hardest challenge turned out to be getting PHP to run in Fast-CGI under IIS 7.0.

As the title suggests PostGIS 1.3.5/PostgreSQL 8.3 seems to work just fine under Windows 2008 Server (well at least 32-bit). Took me all of 10 minutes to download, run the application stack builder. Loading my data took a bit longer, but that is not surprising.

Windows Server 2008 doesn't look all that different from Windows 2003. There is some cross-breeding look between it and Vista, but at least I can more or less find things.

Continue reading "PostGIS on Windows 2008 Server in the Cloud"

Mapserver Cheatsheet

nospam@example.com (Regina Obe) — Tue, 30 Dec 2008 16:19:00 -0500

We started to upgrade all our old mapfiles to the new 5.2 standard because first its about time and now that we are starting to do a non-trivial amount of Mapserver consulting, we felt it important to keep informed.

In doing so we started to develop a cheat sheet to help guide us on what's possible in Mapserver Mapfile 5.2. The cheat sheet can be found here -- keep in mind its still a work in progress so please refrain from laughing. In the middle of making this -- all the mapserver document links changed. I guess Mapserver documentors were busy too. I must say the new documenation is much better than the old. It has some nice examples all nicely color coded. So I want to thank Steve Lime, Jeff Mckenna, Jean-François Doyon and the others in Mapserver doc group for a job well done.

In mapping this cheat sheet out, the first thing slapping me on the side of the head is Mapserver really needs an XML defined schema. Yes I know I sound like a hypocrite and one who has been smoking XSLT a tad too long for my own good. Yes I was on the side of the group of people who thought What is it with these XML idiot lovers. What is to love in such a monstrously verbose unforgiving format? Mapfile format is wonderful, easy to read, fast to parse, and write THANK YOU very much.

Now it appears I am switching camps. Wait a minute though - the truth is I could care less if the Mapserver engine can understand XML. I just want to write my map file in XML and am happy to XSLT it to a format Mapserver can deal with. Why? Do I think XML is a beautiful language? On the contrary -- I find the format stinks and I think it is inefficient in many use-cases. It is in fact the most abused language if there ever was one.

Our main frustration with XML is that in our line of work we do a lot of data loading, data scrubbing and so forth, and we are usually on the side of the fence where some goof-ball gives you some blob of XML with weird characters and other junk and just stuffs it in a CDATA tag and thinks that will solve all issues or where some guy just took OO UML 101 and he has spent a great deal of time over-thinking his data model when time would have been better spent getting loaded at the nearest bar. He has this wonderfully elaborate 30-level portable XML data file for his simple list of jobs complete with 200-pages describing every facet of it for you to digest.

XML is bulky, unforgiving, and easily abused so why is it useful? The beauty of XML and XML XSDs is that a lot of software and development tools support them.

If I had an XSD for mapserver, my editor would immediately tell me what values are valid for each type that takes a fixed number of constants
My editor would immediately tell me when I am stuffing something in a layer that is not valid
I can XSLT my way to a standard mapserver file or god forbid some sort of tree thingy with links to documentation of that element. How many times have you looked at the mapserver documentation and thought? Can I stuff this object in a layer group? Have no clue - the documentation doesn't really tell me that unless the writer happened to remember and even then there is way too much reading involved for my 5-minute attention span to deal with.
Nowadays XSLT joins the group of SQL as an embeddable language. Can you think of a modern language that can't deal with XML/XSLT? In PHP/.NET which we program in predominately, both have XSLT classes that can take any xsl file and xml file and transform. My code editor has a built-in XSL processor.
Mapserver mapfile format just lends itself to that - think nested groups, groups that can go in some groups but not others or can have at most x number of item z in it. Mapserver is far from a simple structure with simple rules, so demands something designed for that kind of thing.
I tend to think it would help the documentation effort immensely too, because right now as the documentation stands, there is not enough cross-referencing going on or an easy way to visualize the natural flow of the map file. An XSLT of an XSD would solve that problem nicely I think. Take for example LABEL. Do you have any clue what elements a label can be stuffed in if you were just starting out?

So to me -- or at least with my understanding of things XML -> MapFile -- fairly trivial, XSD fairly trivial but torturous exercise, MapFile --> XML not so trivial but less torturous exercise. I'm perfectly content with an XML -> MapFile route and am actually thinking about creating such a thing for 5.2 if someone hasn't done it already, if only I could ever find the time, but if things keep going as they are, I may just have to do that if nothing but to keep my sanity.

Chocolate and Peanut Butter Cross-Breeding with PostgreSQL, SQL Server 2008, and Oracle

nospam@example.com (Regina Obe) — Fri, 21 Nov 2008 15:35:00 -0500

I've been whining a lot lately about how SQL Server 2008 (and none of the other SQL Server's) have a generate_series() function that I have grown to love in PostgreSQL. Admittedly I've just been too lazy to create one even though its not that difficult of a task.

Simon Greener over at Spatial DBAdvisor heard my whining and I guess got fed up enough to create a generate_series() function for SQL Server 2008. He also has a generate_series function for Oracle too by the way.

Now there are a couple of differences between the way you use it in the 3 databases which are caused by fundamental differences between the architectures of the 3 databases.

Continue reading "Chocolate and Peanut Butter Cross-Breeding with PostgreSQL, SQL Server 2008, and Oracle"

SQL Server 2008 Part Three

nospam@example.com (Regina Obe) — Tue, 11 Nov 2008 18:43:00 -0500

We have published the first draft of our SQL Server 2008 Spatial Part 3 tutorial. In this part, we demonstrate how to install the SQL Server Spatial Tools which includes the much needed Union aggregate function. More examples of its use will follow.

SQL Server 2008 Spatial First Blush

nospam@example.com (Regina Obe) — Sat, 01 Nov 2008 12:38:00 -0400

We have started to take a really hard look at what SQL Server 2008 offers. Aside from the usual stuff that makes a database lover's heart go thumpety thump -

More SQL constructs
Enhancements to Reporting Services
New expansion of SQL Server database storage to use filesystem for large document storage AKA (FILESTREAM storage)
Powershell integration
Introduction of sparse columns more suitable for datewarehouse type stuff
And of course introduction of spatial which makes a spatial database analysts heart go thumpety thump thump.

and list goes on of course.

To commemorate our tour of the new SQL Server, we have started a new section in BostonGIS called SQL Server 2008 Tutorials and have put in our very first article. Part 1: Getting Started with SQL Server 2008 Spatial: An almost Idiot's Guide. We are still learning so feel free to let us know where we went wrong.

Those who have read our popular Part 1: Getting Started With PostGIS: An almost Idiot's Guide will recognize some similarities and those are for the most part intentional. Does this mean we are abandoning PostGIS? No. We are technologists and implementors and as such, it is always important to have at least 2 answers to every question. For example PostGIS has the advantage of running on more operating systems and a growing and not too shabby ecosystem and a very nimble architecture, while SQL Server 2008 although it runs only on Windows, it has a massive ecosystem behind it from third-party's and Microsoft itself (e.g. SharePoint, CRM, Virtual Earth), which for many of the clientele we deal with are not too shabby things.

It is equally important to stick with standards and themes within reason because standards provide the glue that allows disparate systems to interoperate. Focusing on standards also means the less you need to learn to get stuff done. The main thing we like about both of these platforms, is that they try to stick with ANSI database standards and OGC standards. This is very important because it means there is synergy with learning both. Learning one helps reinforce learning the other - its sort of like analyzing a coin and flipping it on its side. Where the requirements of one project rules out one for one reason or another , the other may still be a likely fit. In fact we might just have some chocolate and peanut butter examples in the wings.

PostGIS Changes and Thanks

nospam@example.com (Regina Obe) — Sun, 19 Oct 2008 07:16:00 -0400

I would like to take this opportunity to bore everyone with changes in PostGIS documentation and my general take on changes in PostGIS/GEOS land. This is to complement Paul Ramsey's series detailing changes to the engine under the hood of the upcoming PostGIS 1.3.4/GEOS 3.0.2 and PostGIS 1.4/GEOS 3.1.0 hot rods.

Martin Davis and Paul Ramsey are also ganging up to entice you with the treasures that can be had for much less than what you lost in the stock market or the amount you pay in licenses and maintenance to your commercial database feudal lords. PostGIS is not a complete drop-in-replacement for all spatial database use-cases, but I would say it fits the 80%-90% mark and even more with some clever hacks. It is definitely getting better with each iteration. User comments and our own personal experience suggests its speed, ease of use, and flexibility rocks above most commercial offerings. With a little bit more contribution of time and money from the user community, it will be an even greater force to be reconned with- The David of the spatial database fighting ring; Lean, mean, and a fierce fighting machine.

Consider that PostGIS is probably as fast or already faster than what you are currently using for spatial manipulation and analysis. Imagine what you can do with getting even 10-20 times faster speed on top of that - Cascaded Union or 20 times faster Buffering. Those are merely low hanging fruit. Higher fruit remains to be had - such as improved 3D support, Circular String support, and Geodetic Support.

Getting back to the documentation thing. Well it is not quite as exciting as that revved-up engine Paul and Martin have been yapping about, but nothing complements a well-primed engine than a great user's manual to guide you thru the controls. The upcoming 1.4 HTML and 1.4 PDF will hopefully be just that. In particular the new PostGIS reference section is being expanded on and once we have that stabilized we'll have nice color glossy pictures to blow your mind.

Kevin Neufeld et. al, have been revamping the documentation for 1.4, and as a result of this much better organized doc book structure, we have been able to produce some useful derivative products which I shall refer to as documentation meatloaf. One of these meatloaf products is postgis_comments.sql which is packaged in the 1.3.4SVN install. Why is this meatloaf? Because with a simple XSL transform of the new reference section, this derivative file was created and PostgreSQL does something useful with it to help you navigate the growing plethora of hot rod PostGIS functions.

If you load this up in your PostGIS-enabled spatial database, you will be swept away with fantastic scenes such as these.

For the PgAdmin III automatic-drive wimps

For the psql command-line stick-shift masochists

testpostgis-# \dd *make*
                           Object descriptions
   Schema   |      Name      |  Object   |                                 Description
------------+----------------+-----------+-------------------------------------------------------------
 pg_catalog | makeaclitem    | function  | make ACL item
 public     | st_makeline    | aggregate | args: pointfield - Creates a Linestring from point geometries.
 public     | st_makeline    | function  | args: point1, point2 - Creates a Linestring from point geometries.
 public     | st_makepoint   | function  | args: x, y - Creates a 2d,3dz or 4d point geometry.
 public     | st_makepoint   | function  | args: x, y, z - Creates a 2d,3dz or 4d point geometry.
 public     | st_makepoint   | function  | args: x, y, z, m - Creates a 2d,3dz or 4d point geometry.
 public     | st_makepointm  | function  | args: x, y, m - Creates a point geometry with 
an x y and m coordinate.
 public     | st_makepolygon | function  | args: linestring - Creates a Polygon 
formed by the given shell. 
 Input geometries must be closed LINESTRINGS.
 public     | st_makepolygon | function  | args: outerlinestring, interiorlinestrings - 
 Creates a Polygon formed by the given shell. 
 Input geometries must be closed LINESTRINGS.

The manual and the generated sql description file are still a work in progress, so don't be too upset if you don't see your favorite function extensively documented.

I would like to thank the following folks who have helped make the upcoming manual and PostGIS a huge success already. Granted there are still a lot of gaps to be filled in.

Kevin Neufeld, who came up with the slick page template for each help function, that makes it trivially easy to parse into derivative products and who has also been diligently documenting away and sprinkling with full oo-la-la color pictures and who set up the hudson builder to autobuild the docs and tar packages, that as a side benefit also alarms us when someone breaks the build.
Dane Springmeyer, who modified the CSS to give a more professional look and also did a prototype meatloaf implementation based on the old documentation style that fully demonstrated how badly we needed a new structure.
Jean David Techer, who provided his install instructions for generating a slick-looking PDF output doc.
Paul Ramsey, who created the first manual way back when and proclaimed - All it needs is getting someone's nose to the grindstone and then promptly dashed off to do tasks much more boring, but admittedly more heroic; chasing memory leaks and inefficiencies in logic. I personally can't think of a more depressing way to spend my time than chasing &, *, delete calls. Sounds almost as much fun as being disemboweled while awake. Go Go Braveheart
Mark Cave-Ayland for stream-lining the code base, keeping law and order, and quickly knocking down Paul's (aka the cowboy) occassional half-baked ideas. We really feel the pain when Mark steps out on a holiday .
To GEOS team - Martin Davis (aka Dr. JTS for his technical genius), Mateusz Loskot (who has been cleaning leaks and stuff getting it ready for release), Hartmut Kaiser who contributed a small but important leak patch, Safe Software who has already pledged sponsorship.
To PostgreSQL and the whole PostgreSQL community, for providing the great database infrastructure behind PostGIS.
And most importantly, to the great OSGEO/PostGIS community of testers, package maintainers, bloggers, and patch developers for spreading the gospel and working to make the next release a great success.

PostGIS shp2pgsql cheat sheet, OpenJump Quick Glance

nospam@example.com (Regina Obe) — Mon, 15 Sep 2008 02:18:00 -0400

We've been busy as of late, so has been hard to keep up with blogging. As many of you may notice, we have another satellite site called Postgres OnLine Journal. We try to put only PostgreSQL specific things on that site and PostGIS/GIS things on this site, but often times the lines cross and so we end up cross-linking. This is one of those times.

Cheat Sheets

People who know me tend to think I have a spectacular memory for things. What they don't seem to realize is I have good recollection but bad memory. People seem to have a hard time differentiating between the two concepts. My natural survival instinct to compensate for my terrible memory is to carefully organize my thoughts which often gives the illusion of a spectacular memory in many cases and embarassing absent-mindedness in others.

This also makes me more sensitive to breaks in patterns than most and produces some other interesting and humorous side effects.

One of the tools I use to organize my memory is to create cheat sheets which I gain some joy in making. Cheat sheets provide a reference as well as forces a certain level of knowledge organization. Here are some that PostGIS users may find useful.

Recently Created
PostGIS 1.3.3 pgsql2shp shp2pgsql Command Line Cheatsheet
PostgreSQL Pg_dump Pg_Restore Cheatsheet
PostgreSQL 8.3 Cheat Sheet
Old one which I hope to update when the new release comes out
PostGIS ver. 1.3.1 Quick Guide

Yeh for OpenJUMP

I'm still navigating my way thru the OpenJUMP tool, but so far I like it more than even the commercial tools I have used and even more than QuantumGIS. Although doesn't seem to have a shape importer similar to QuantumGIS's SPIT plug-in or Manifold's link import tools.

So far I have tried to try ESRI ArcGIS, Manifold.net, GvSig, uDig. Manifold seemed to have some interesting features, but required some setting back and reading material which I have had too short of an attention span for. Granted looks like it would be worth it once I can get myself to sit down for longer than 15 minutes.

ESRI ArcGIS layout and terminology, sorry if I am offending anyone here, just seems altogether confusing to me which makes me think what kind of person came up with this chaotic structure?. Perhaps its just a side-effect of consolidating a number of pseudo-disparate products over the years under one umbrella. I'm sure there is beauty in there that I am just not able to appreciate coming from GIS from a non-standard angle.

GvSig, admittedly I haven't explored enough. The layout seemed organized enough, but just not my kind of layout.

uDig I haven't tried in a while, but seemed a bit overwhelming and when I did, I found it annoying that it would arbitrarily pick a geometry field if I had more than one in a table and I couldn't seem to selectively select one. That was a couple of years ago so things probably changed, and I really should give it another go.

OpenJUMP on the other hand was like it just read my mind. I really haven't had to read anything to do the things I have wanted to do in it. Well really most of what I was looking for was an ad-hoc spatial query tool so while Manifold and ArcGIS probably have tons of useful stuff that are real gems, it gets in my way of my short-term objectives. Plus the OpenJUMP mascot was an added benefit since it is similar to the logo of our biggest client and a graphic representation of my old moniker - which was derived from my initials. So OpenJump gets plus 2 for nostalgic effect.

If anyone is interested, I've put together a quick brush through of using it - at our Postgres Online Journal satellite site - in the product showcase section.
OpenJump for PostGIS Spatial Ad-Hoc Queries

Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6

nospam@example.com (Regina Obe) — Sat, 19 Jul 2008 18:25:00 -0400

A few people have been asking us what are the pros and cons of using SQL Server 2008 Spatial and PostGIS and as a Windows user, why would you still consider using PostGIS. Rather than simply providing some hand-waving saying "well if you just care about displaying data, then use whatever you feel comfortable with, but if you want to do real intensive sophisticated spatial analysis and geometric processing without having to purchase a bunch of expensive software, then PostGIS is probably better for you. Hell why must you think in either or propositions - just use both using the strengths of each.", we have tried really hard to quantify the similarities and differences between the 2 and to boot - we have also added in MySQL.

Our analysis can be found at Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6

If you have any comments, suggestions of additions, things you felt we got wrong, then please don't hesitate to comment and we'll try to update our survey.