Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 .

share|improve this question
    
Please show us the definition (DDL) of the view. –  a_horse_with_no_name Sep 2 '13 at 15:00
add comment

1 Answer

up vote 1 down vote accepted

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).

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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