[postgis-users] Not able to excute spatial query on postgresql database with postgis support

[vamsee movva]

Hi Michael,
              Thank you for your reply.
Here are  results of the queries you mentioned.
AsText() and AsEWKT()
select AsEWKT(the_geom) from la_blocks where stfid='220750501002995';

asewkt
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 MULTIPOLYGON(((-89.826276 29.643064,-89.826879 29.642835,-89.827639
29.642766,-89.828321 29.642949,-89.828609 29.642858,-89.828635 29.641735,-
89.82895 29.6413,-89.829579 29.641323,-89.830628 29.641598,-89.831703
29.642079,-89.832384 29.642559,-89.833171 29.643315,-89.833617 29.643452,-
89.834429 29.643407,-89.835033 29.643819,-89.835164 29.644093,-89.835111
29.644414,-89.835033 29.644529,-89.834666 29.644712,-89.834456 29.644941,-
89.834351 29.645262,-89.834378 29.645743,-89.834719 29.646178,-89.834876
29.646338,-89.834928 29.646636,-89.834719 29.646888,-89.834352 29.646957,-
89.833985 29.646865,-89.833617 29.646636,-89.832936 29.646064,-89.832595
29.645835,-89.832018 29.64572,-89.831389 29.64572,-89.830655 29.646064,-
89.829763 29.646156,-89.828924 29.645995,-89.828269 29.645583,-89.82743
29.64485,-89.827141 29.644415,-89.826931 29.643911,-89.826722 29.64382,-
89.826617 29.643889,-89.826355 29.644324,-89.826302 29.64437,-89.826224
29.644507,-89.826014 29.644622,-89.825725 29.644622,-89.825647 29.644232,-
89.825883 29.643751,-89.826066 29.643454,-89.826197 29.643316,-89.826276
29.643064),(-89.831179 29.643567,-89.830287 29.643682,-89.829946 29.643911,-
89.82992 29.644026,-89.830182 29.644186,-89.831231 29.644735,-89.831467
29.644644,-89.831546 29.644438,-89.831546 29.64398,-89.831441 29.643773,-
89.831179 29.643567)))
(1 row)

(FOR stfid 220750501002999,It gave a very big set of co-ordinates)
SELECT stfid, npoints(the_geom), nrings(the_geom) FROM la_blocks ORDER BY
nrings DESC LIMIT 10;
      stfid      | npoints | nrings
-----------------+---------+--------
 220750501002999 |   64266 |   2507
 220750501002103 |   60919 |   2487
 220750508001999 |   19019 |    821
 220750508001000 |   16480 |    813
 220750501002998 |   45421 |    731
 221090013004990 |   34863 |    354
 221090011003999 |   17073 |    337
 221090012024005 |   13503 |    290
 220870301012999 |   44032 |    286
 220750501002065 |   12727 |    248
(10 rows)

These are the points where the query taking too much time to execute.

SELECT getbbox(the_geom) FROM la_blocks WHERE stfid = '220750501002999';
                                  getbbox
----------------------------------------------------------------------------
 BOX(-89.5361709594727 28.8551273345947,-88.8792343139648 29.6479110717773)
(1 row)

I think, the number of rings is the problem
Thanks alot,
have a nice day
vamsee movva






On 11/1/06, Michael Fuhr <mike at fuhr.org> wrote:
>
> On Tue, Oct 31, 2006 at 10:47:50AM -0600, vamsee movva wrote:
> > > What can you tell us about the geometries in la_damage ?
> >
> > Honestly i don't have any idea about geometries and how to find
> geometries.
> > the geometry in la_blocks for stfid = 220750501002999?
> > I don't know how to see the geometry of the_geom column.
>
> You can see a geometry's textual representation with functions like
> AsText() and AsEWKT() or you could see graphical output with various
> visualization tools.
>
> > > What's the output of the following query?
> >
> > SELECT summary(the_geom) FROM la_blocks WHERE stfid = '220750501002999';
> > please find the attached file "stfid.txt"
>
> Ouch:
>
> > MultiPolygon[B] with 1 elements
> >   Polygon[] with 2507 rings
>
> I count 64266 points altogether.  Does this geometry's row in
> la_blocks contain other columns that explain what it is?  Is this
> geometry's size an anomaly?  The following queries should show
> the geometries with the most rings and points:
>
> SELECT stid, npoints(the_geom), nrings(the_geom)
> FROM la_blocks
> ORDER BY nrings DESC
> LIMIT 10;
>
> SELECT stid, npoints(the_geom), nrings(the_geom)
> FROM la_blocks
> ORDER BY npoints DESC
> LIMIT 10;
>
> Examining the geometry's bounding box might also be revealing:
>
> SELECT getbbox(the_geom)
> FROM la_blocks
> WHERE stfid = '220750501002999';
>
> I'm wondering if this geometry is representative of the rest of the
> data or if it's an outlier and possibly bogus.
>
> --
> Michael Fuhr
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20061101/7d4b5637/attachment.html>