0

I have a view openstreetmapview that combines two tables with different columns. The first table is openstreetmap and the second is B.

When I execute this query on the view:

select this_.gid             as y0_
     , this_.openstreetmapId as y1_
     , this_.name            as y2_
     , this_.streetType      as y3_
     , this_.oneWay          as y4_
     , this_.location        as y5_
     , this_.isIn            as y6_
     , this_.countryCode     as y7_
     , length                as y8_
     , distance_sphere(
           GeometryFromText(
               'POINT(11.059734344482422 46.07373046875)',4326)
             , ST_line_interpolate_point(
                       this_.shape
                      , st_line_locate_point(
                             this_.shape
                           , GeometryFromText('POINT(11.059734344482422 46.07373046875)', 4326)
                      )
                )
       ) as y9_
     , ST_AsText(ST_ClosestPoint(
               this_.shape,GeometryFromText( 'POINT(11.059734344482422 46.07373046875)', 4326)
       )) as y10_ 
from OpenStreetMapView this_ ;

Then, I get around 50k results. When I execute the same query on the table even though the table has the same column that the query needs, it returns 0 rows. Why is that?

I use PostgreSQL 8.4 database and PostGIS .

1
  • Please show us the definition (DDL) of the view.
    – user1822
    Commented Sep 2, 2013 at 15:00

1 Answer 1

1

As you have absolutely no filtering clauses (no WHERE clauses or JOINs that would filter the rows from the base table) the only reason you should get zero rows from that query is that there are zero rows in the table for it to return.

SELECT COUNT(*) FROM OpenStreetMapView will confirm or contradict this.

If a process that should have populated the table has not yet completed other transactions may not be able to see the data yet (until that transaction is complete), though I believe in this case the default locking and transaction isolation settings would cause either an error to be raised or a sleep to be imposed for the checking query until the other process is complete (rather than returning nothing).

2
  • Although this is absolutely ridiculous, it's quite true and very logical reasoning!
    – M-T-A
    Commented Sep 2, 2013 at 16:26
  • Hibernate was truncating/creating database every time time the application starts.
    – M-T-A
    Commented Sep 2, 2013 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.