In PostgreSQL 9.0+, the default encoding for bytea data has been changed to hex and older JDBC drivers still assume escape format. This has affected some applications such as Java applications using older JDBC drivers or .NET applications that use the older npgsql driver that expect the old behavior of ST_AsBinary. There are two approaches to getting this to work again.

You can upgrade your JDBC driver to the latest PostgreSQL 9.0 version which you can get from http://jdbc.postgresql.org/download.html

If you are running a .NET app, you can use Npgsql 2.0.11 or higher which you can download from http://pgfoundry.org/frs/?group_id=1000140 and as described on Francisco Figueiredo's NpgSQL 2.0.11 released blog entry

If upgrading your PostgreSQL driver is not an option, then you can set the default back to the old behavior with the following change:

ALTER DATABASE mypostgisdb SET bytea_output='escape';

PgAdmin doesn't show anything for large geometries. The best ways to verify you do have day in your geometry columns are?

-- this should return no records if all your geom fields are filled in        
SELECT somefield FROM mytable WHERE geom IS NULL;

-- To tell just how large your geometry is do a query of the form
--which will tell you the most number of points you have in any of your geometry columns
SELECT MAX(ST_NPoints(geom)) FROM sometable;

You can store point, line, polygon, multipoint, multiline, multipolygon, and geometrycollections. These are specified in the Open GIS Well Known Text Format (with XYZ,XYM,XYZM extensions). There are two data types currently supported. The standard OGC geometry data type which uses a planar coordinate system for measurement and the geography data type which uses a geodetic coordinate system. Only WGS 84 long lat (SRID:4326) is supported by the geography data type.

Short Answer: geography is a new data type that supports long range distances measurements, but most computations on it are currently slower than they are on geometry. If you use geography -- you don't need to learn much about planar coordinate systems. Geography is generally best if all you care about is measuring distances and lengths and you have data from all over the world. Geometry data type is an older data type that has many more functions supporting it, enjoys greater support from third party tools, and operations on it are generally faster -- sometimes as much as 10 fold faster for larger geometries. Geometry is best if you are pretty comfortable with spatial reference systems or you are dealing with localized data where all your data fits in a single spatial reference system (SRID), or you need to do a lot of spatial processing. Note: It is fairly easy to do one-off conversions between the two types to gain the benefits of each. Refer to Section 8.8, “PostGIS Function Support Matrix” to see what is currently supported and what is not.

Long Answer: Refer to our more lengthy discussion in the Section 4.2.2, “When to use Geography Data type over Geometry data type” and function type matrix.

Your questions are too deep and complex to be adequately answered in this section. Please refer to our Section 4.2.3, “Geography Advanced FAQ” .

First, you need to create a table with a column of type "geometry" or "geography" to hold your GIS data. Storing geography type data is a little different than storing geometry. Refer to Section 4.2.1, “Geography Basics” for details on storing geography.

For geometry: Connect to your database with psql and try the following SQL:

CREATE TABLE gtest ( ID int4, NAME varchar(20) );
SELECT AddGeometryColumn('', 'gtest','geom',-1,'LINESTRING',2);

If the geometry column addition fails, you probably have not loaded the PostGIS functions and objects into this database. See the Section 2.4, “Installation”.

Then, you can insert a geometry into the table using a SQL insert statement. The GIS object itself is formatted using the OpenGIS Consortium "well-known text" format:

INSERT INTO gtest (ID, NAME, GEOM) 
VALUES (
  1, 
  'First Geometry', 
  ST_GeomFromText('LINESTRING(2 3,4 5,6 5,7 8)', -1)
);

For more information about other GIS objects, see the object reference.

To view your GIS data in the table:

SELECT id, name, ST_AsText(geom) AS geom FROM gtest;

The return value should look something like this:

 id | name           | geom
----+----------------+-----------------------------
  1 | First Geometry | LINESTRING(2 3,4 5,6 5,7 8) 
(1 row)

The same way you construct any other database query, as an SQL combination of return values, functions, and boolean tests.

For spatial queries, there are two issues that are important to keep in mind while constructing your query: is there a spatial index you can make use of; and, are you doing expensive calculations on a large number of geometries.

In general, you will want to use the "intersects operator" (&&) which tests whether the bounding boxes of features intersect. The reason the && operator is useful is because if a spatial index is available to speed up the test, the && operator will make use of this. This can make queries much much faster.

You will also make use of spatial functions, such as Distance(), ST_Intersects(), ST_Contains() and ST_Within(), among others, to narrow down the results of your search. Most spatial queries include both an indexed test and a spatial function test. The index test serves to limit the number of return tuples to only tuples that might meet the condition of interest. The spatial functions are then use to test the condition exactly.

SELECT id, the_geom 
FROM thetable 
WHERE 
  ST_Contains(the_geom,'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))');

Fast queries on large tables is the raison d'etre of spatial databases (along with transaction support) so having a good index is important.

To build a spatial index on a table with a geometry column, use the "CREATE INDEX" function as follows:

CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometrycolumn] );

The "USING GIST" option tells the server to use a GiST (Generalized Search Tree) index.

	GiST indexes are assumed to be lossy. Lossy indexes uses a proxy object (in the spatial case, a bounding box) for building the index.

You should also ensure that the PostgreSQL query planner has enough information about your index to make rational decisions about when to use it. To do this, you have to "gather statistics" on your geometry tables.

For PostgreSQL 8.0.x and greater, just run the VACUUM ANALYZE command.

For PostgreSQL 7.4.x and below, run the SELECT UPDATE_GEOMETRY_STATS() command.

Early versions of PostGIS used the PostgreSQL R-Tree indexes. However, PostgreSQL R-Trees have been completely discarded since version 0.6, and spatial indexing is provided with an R-Tree-over-GiST scheme.

Our tests have shown search speed for native R-Tree and GiST to be comparable. Native PostgreSQL R-Trees have two limitations which make them undesirable for use with GIS features (note that these limitations are due to the current PostgreSQL native R-Tree implementation, not the R-Tree concept in general):

If you do not want to use the OpenGIS support functions, you do not have to. Simply create tables as in older versions, defining your geometry columns in the CREATE statement. All your geometries will have SRIDs of -1, and the OpenGIS meta-data tables will not be filled in properly. However, this will cause most applications based on PostGIS to fail, and it is generally suggested that you do use AddGeometryColumn() to create geometry tables.

MapServer is one application which makes use of the geometry_columns meta-data. Specifically, MapServer can use the SRID of the geometry column to do on-the-fly reprojection of features into the correct map projection.

To use the database most efficiently, it is best to do radius queries which combine the radius test with a bounding box test: the bounding box test uses the spatial index, giving fast access to a subset of data which the radius test is then applied to.

The ST_DWithin(geometry, geometry, distance) function is a handy way of performing an indexed distance search. It works by creating a search rectangle large enough to enclose the distance radius, then performing an exact distance search on the indexed subset of results.

For example, to find all objects with 100 meters of POINT(1000 1000) the following query would work well:

SELECT * FROM geotable 
WHERE ST_DWithin(geocolumn, 'POINT(1000 1000)', 100.0);

To perform a reprojection, both the source and destination coordinate systems must be defined in the SPATIAL_REF_SYS table, and the geometries being reprojected must already have an SRID set on them. Once that is done, a reprojection is as simple as referring to the desired destination SRID. The below projects a geometry to NAD 83 long lat. The below will only work if the srid of the_geom is not -1 (not undefined spatial ref)

SELECT ST_Transform(the_geom,4269) FROM geotable;

You are probably using PgAdmin or some other tool that doesn't output large text. If your geometry is big enough, it will appear blank in these tools. Use PSQL if you really need to see it or output it in WKT.

				--To check number of geometries are really blank
				SELECT count(gid) FROM geotable WHERE the_geom IS NULL;

This generally happens in two common cases. Your geometry is invalid -- check ST_IsValid or you are assuming they intersect because ST_AsText truncates the numbers and you have lots of decimals after it is not showing you.