[postgis-users] no row selected on spatial query

[Puneet Kishor]

A brief follow-up on this --

On Oct 27, 2011, at 5:57 PM, Puneet Kishor wrote:

> I inherited two tables like so
> 
> 	db=# select ST_Extent(the_geom) from t1;
> 	                    st_extent                    
> 	-------------------------------------------------
> 	 BOX(-180 5.67656603969958,180 89.9999951703269)
> 	(1 row)
> 
> 	db=# select ST_Extent(the_geom) from t2;
> 	                         st_extent                         
> 	-----------------------------------------------------------
> 	 BOX(-3666182.8927 1296299.9824,4440422.8742 8820425.3008)
> 	(1 row)
> 
> I want to convert t2 to match t1. So, I tried the following --
> 
> 	db=# UPDATE t2 SET the_geom = ST_Transform(the_geom, 4326);
> 	UPDATE 1354
> 
> 
> But that didn't do it.
> 
> 	db=# select ST_Extent(the_geom) from t2;
> 	                         st_extent                         
> 	-----------------------------------------------------------
> 	 BOX(-3666182.8927 1296299.9824,4440422.8742 8820425.3008)
> 	(1 row)
> 
> 
> What do I do to transform t2 so its geometry matches t1?
> 

Turns out t2 was loaded from a shapefile using shp2pgsql using SRID 4326. Its table definition is like so

CREATE TABLE t2
(
 gid serial NOT NULL,
 objectid integer,
 type integer,
 the_geom geometry,
 CONSTRAINT t2_pkey PRIMARY KEY (gid ),
 CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
 CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL),
 CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)
)
WITH (
 OIDS=FALSE
);

So, in spite of the `enforce_srid_the_geom` CONSTRAINT which should restrict geometry to 4326, seems like meter values got inserted into the table. How is that possible?

And, in any case, how can I repair this?

--
Puneet Kishor