Revision 47579 trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.sqlite/org.gvsig.sqlite.provider/src/test/resources/org/gvsig/sqlite/dal/testSTDistance.sql

View differences:

testSTDistance.sql
1 1 

  		




  2
  2
  
    
-- testSTDistance SQL


  		




  3
  3
  
    

  		




  4
  
  
    
-- testSTDistance


  		




  
  4
  
    
-- testSTDistanceWithoutSpatialIndex


  		




  5
  5
  
    
SELECT 


  		




  6
  
  
    
    "PUBLIC"."test"."ID", "PUBLIC"."test"."Byte", "PUBLIC"."test"."Bool1", "PUBLIC"."test"."Long", 


  		




  7
  
  
    
    "PUBLIC"."test"."Timestamp", "PUBLIC"."test"."Date", "PUBLIC"."test"."Time", "PUBLIC"."test"."Bool2", 


  		




  8
  
  
    
    "PUBLIC"."test"."String", "PUBLIC"."test"."Bool3", "PUBLIC"."test"."Double", "PUBLIC"."test"."Bool4", 


  		




  9
  
  
    
    "PUBLIC"."test"."Float", "PUBLIC"."test"."Bool5", "PUBLIC"."test"."Decimal", 


  		




  10
  
  
    
    NVL2("PUBLIC"."test"."Geometry",ST_AsBinary("PUBLIC"."test"."Geometry"),NULL) 


  		




  11
  
  
    
FROM "PUBLIC"."test" 


  		




  12
  
  
    
WHERE ( ((ST_GeomFromWKB(X'00000000030000000100000021402600000000000040240000000000004025f6297cff75cb40239c1d1f0e59684025d906bcf328d440233c10eaca8ab54025a9b66290ea1a4022e38c4c65197440256a09e667f3bd402295f619980c4340251c73b39ae68d402256499d6f15e64024c3ef1535754b402226f9430cd72c402463e2e0f1a698402209d683008a354024000000000000402200000000000040239c1d1f0e5968402209d683008a3540233c10eaca8ab5402226f9430cd72c4022e38c4c651974402256499d6f15e6402295f619980c43402295f619980c43402256499d6f15e64022e38c4c651974402226f9430cd72c40233c10eaca8ab5402209d683008a3540239c1d1f0e596840220000000000004024000000000000402209d683008a35402463e2e0f1a699402226f9430cd72c4024c3ef1535754c402256499d6f15e640251c73b39ae68d402295f619980c4440256a09e667f3bd4022e38c4c6519744025a9b66290ea1b40233c10eaca8ab64025d906bcf328d540239c1d1f0e59694025f6297cff75cb40240000000000014026000000000000402463e2e0f1a69a4025f6297cff75cb4024c3ef1535754d4025d906bcf328d440251c73b39ae68e4025a9b66290ea1940256a09e667f3be40256a09e667f3bb4025a9b66290ea1b40251c73b39ae68b4025d906bcf328d54024c3ef153575494025f6297cff75cb402463e2e0f1a69640260000000000004024000000000000', 4326)) && ("PUBLIC"."test"."Geometry")) AND 


  		




  13
  
  
    
    ST_Intersects((ST_GeomFromWKB(X'00000000030000000100000021402600000000000040240000000000004025f6297cff75cb40239c1d1f0e59684025d906bcf328d440233c10eaca8ab54025a9b66290ea1a4022e38c4c65197440256a09e667f3bd402295f619980c4340251c73b39ae68d402256499d6f15e64024c3ef1535754b402226f9430cd72c402463e2e0f1a698402209d683008a354024000000000000402200000000000040239c1d1f0e5968402209d683008a3540233c10eaca8ab5402226f9430cd72c4022e38c4c651974402256499d6f15e6402295f619980c43402295f619980c43402256499d6f15e64022e38c4c651974402226f9430cd72c40233c10eaca8ab5402209d683008a3540239c1d1f0e596840220000000000004024000000000000402209d683008a35402463e2e0f1a699402226f9430cd72c4024c3ef1535754c402256499d6f15e640251c73b39ae68d402295f619980c4440256a09e667f3bd4022e38c4c6519744025a9b66290ea1b40233c10eaca8ab64025d906bcf328d540239c1d1f0e59694025f6297cff75cb40240000000000014026000000000000402463e2e0f1a69a4025f6297cff75cb4024c3ef1535754d4025d906bcf328d440251c73b39ae68e4025a9b66290ea1940256a09e667f3be40256a09e667f3bb4025a9b66290ea1b40251c73b39ae68b4025d906bcf328d54024c3ef153575494025f6297cff75cb402463e2e0f1a69640260000000000004024000000000000', 4326)),("PUBLIC"."test"."Geometry") )) 


  		




  14
  
  
    
ORDER BY ST_Distance(ST_GeomFromWKB(X'00000000030000000100000021402600000000000040240000000000004025f6297cff75cb40239c1d1f0e59684025d906bcf328d440233c10eaca8ab54025a9b66290ea1a4022e38c4c65197440256a09e667f3bd402295f619980c4340251c73b39ae68d402256499d6f15e64024c3ef1535754b402226f9430cd72c402463e2e0f1a698402209d683008a354024000000000000402200000000000040239c1d1f0e5968402209d683008a3540233c10eaca8ab5402226f9430cd72c4022e38c4c651974402256499d6f15e6402295f619980c43402295f619980c43402256499d6f15e64022e38c4c651974402226f9430cd72c40233c10eaca8ab5402209d683008a3540239c1d1f0e596840220000000000004024000000000000402209d683008a35402463e2e0f1a699402226f9430cd72c4024c3ef1535754c402256499d6f15e640251c73b39ae68d402295f619980c4440256a09e667f3bd4022e38c4c6519744025a9b66290ea1b40233c10eaca8ab64025d906bcf328d540239c1d1f0e59694025f6297cff75cb40240000000000014026000000000000402463e2e0f1a69a4025f6297cff75cb4024c3ef1535754d4025d906bcf328d440251c73b39ae68e4025a9b66290ea1940256a09e667f3be40256a09e667f3bb4025a9b66290ea1b40251c73b39ae68b4025d906bcf328d54024c3ef153575494025f6297cff75cb402463e2e0f1a69640260000000000004024000000000000', 4326), "Geometry") ASC NULLS LAST, 


  		




  15
  
  
    
    "PUBLIC"."test"."ID" ASC 


  		




  16
  
  
    
LIMIT 1;


  		




  
  6
  
    
    "test"."ID", "test"."Byte", "test"."Bool1", "test"."Long", 


  		




  
  7
  
    
    "test"."Timestamp", "test"."Date", "test"."Time", "test"."Bool2", 


  		




  
  8
  
    
    "test"."String", "test"."Bool3", "test"."Double", "test"."Bool4", 


  		




  
  9
  
    
    "test"."Float", "test"."Bool5", "test"."Decimal", "test"."Geometry" 


  		




  
  10
  
    
FROM "test" 


  		




  
  11
  
    
WHERE ST_Intersects((ST_GeomFromWKB((X'000000000300000001000000213ff000000000000040440000000000003fef6297cff75cb04043e70747c3965a3fed906bcf328d464043cf043ab2a2ad3fea9b66290ea1a34043b8e31319465d3fe6a09e667f3bcd4043a57d866603113fe1c73b39ae68c940439592675bc5793fd87de2a6aea964404389be50c335cb3fc8f8b83c69a60d40438275a0c0228d3c91a62633145c074043800000000000bfc8f8b83c69a60840438275a0c0228dbfd87de2a6aea962404389be50c335cbbfe1c73b39ae68c640439592675bc579bfe6a09e667f3bcc4043a57d86660311bfea9b66290ea1a44043b8e31319465dbfed906bcf328d474043cf043ab2a2adbfef6297cff75cb14043e70747c3965abff00000000000004044000000000000bfef6297cff75cae404418f8b83c69a6bfed906bcf328d42404430fbc54d5d53bfea9b66290ea19c4044471cece6b9a3bfe6a09e667f3bc240445a827999fcefbfe1c73b39ae68bb40446a6d98a43a87bfd87de2a6aea94140447641af3cca35bfc8f8b83c69a5bb40447d8a5f3fdd733ce6586c6b36175f40448000000000003fc8f8b83c69a66a40447d8a5f3fdd733fd87de2a6aea99340447641af3cca353fe1c73b39ae68e040446a6d98a43a863fe6a09e667f3be240445a827999fcef3fea9b66290ea1b54044471cece6b9a33fed906bcf328d53404430fbc54d5d523fef6297cff75cb7404418f8b83c69a53ff00000000000004044000000000000'), 4326)),("test"."Geometry")) 


  		




  
  12
  
    
ORDER BY ST_Distance(ST_GeomFromWKB((X'000000000300000001000000213ff000000000000040440000000000003fef6297cff75cb04043e70747c3965a3fed906bcf328d464043cf043ab2a2ad3fea9b66290ea1a34043b8e31319465d3fe6a09e667f3bcd4043a57d866603113fe1c73b39ae68c940439592675bc5793fd87de2a6aea964404389be50c335cb3fc8f8b83c69a60d40438275a0c0228d3c91a62633145c074043800000000000bfc8f8b83c69a60840438275a0c0228dbfd87de2a6aea962404389be50c335cbbfe1c73b39ae68c640439592675bc579bfe6a09e667f3bcc4043a57d86660311bfea9b66290ea1a44043b8e31319465dbfed906bcf328d474043cf043ab2a2adbfef6297cff75cb14043e70747c3965abff00000000000004044000000000000bfef6297cff75cae404418f8b83c69a6bfed906bcf328d42404430fbc54d5d53bfea9b66290ea19c4044471cece6b9a3bfe6a09e667f3bc240445a827999fcefbfe1c73b39ae68bb40446a6d98a43a87bfd87de2a6aea94140447641af3cca35bfc8f8b83c69a5bb40447d8a5f3fdd733ce6586c6b36175f40448000000000003fc8f8b83c69a66a40447d8a5f3fdd733fd87de2a6aea99340447641af3cca353fe1c73b39ae68e040446a6d98a43a863fe6a09e667f3be240445a827999fcef3fea9b66290ea1b54044471cece6b9a33fed906bcf328d53404430fbc54d5d523fef6297cff75cb7404418f8b83c69a53ff00000000000004044000000000000'), 4326), "Geometry") ASC NULLS LAST, 


  		




  
  13
  
    
    "test"."ID" ASC LIMIT 1;


  		




  
  14
  
    

  		




  
  15
  
    
-- testSTDistanceWithSpatialIndex


  		




  
  16
  
    
SELECT 


  		




  
  17
  
    
    "test"."ID", "test"."Byte", "test"."Bool1", "test"."Long", 


  		




  
  18
  
    
    "test"."Timestamp", "test"."Date", "test"."Time", "test"."Bool2", 


  		




  
  19
  
    
    "test"."String", "test"."Bool3", "test"."Double", "test"."Bool4", 


  		




  
  20
  
    
    "test"."Float", "test"."Bool5", "test"."Decimal", "test"."Geometry" FROM "test" WHERE ( (("ID" IN (SELECT "rtree_test_Geometry"."id" 


  		




  
  21
  
    
FROM "rtree_test_Geometry" 


  		




  
  22
  
    
WHERE "rtree_test_Geometry"."minx" <= (1.0) AND 


  		




  
  23
  
    
    "rtree_test_Geometry"."miny" <= (41.0) AND 


  		




  
  24
  
    
    "rtree_test_Geometry"."maxx" >= (-1.0) AND 


  		




  
  25
  
    
    "rtree_test_Geometry"."maxy" >= (39.0)))) AND 


  		




  
  26
  
    
    ST_Intersects((ST_GeomFromWKB((X'000000000300000001000000213ff000000000000040440000000000003fef6297cff75cb04043e70747c3965a3fed906bcf328d464043cf043ab2a2ad3fea9b66290ea1a34043b8e31319465d3fe6a09e667f3bcd4043a57d866603113fe1c73b39ae68c940439592675bc5793fd87de2a6aea964404389be50c335cb3fc8f8b83c69a60d40438275a0c0228d3c91a62633145c074043800000000000bfc8f8b83c69a60840438275a0c0228dbfd87de2a6aea962404389be50c335cbbfe1c73b39ae68c640439592675bc579bfe6a09e667f3bcc4043a57d86660311bfea9b66290ea1a44043b8e31319465dbfed906bcf328d474043cf043ab2a2adbfef6297cff75cb14043e70747c3965abff00000000000004044000000000000bfef6297cff75cae404418f8b83c69a6bfed906bcf328d42404430fbc54d5d53bfea9b66290ea19c4044471cece6b9a3bfe6a09e667f3bc240445a827999fcefbfe1c73b39ae68bb40446a6d98a43a87bfd87de2a6aea94140447641af3cca35bfc8f8b83c69a5bb40447d8a5f3fdd733ce6586c6b36175f40448000000000003fc8f8b83c69a66a40447d8a5f3fdd733fd87de2a6aea99340447641af3cca353fe1c73b39ae68e040446a6d98a43a863fe6a09e667f3be240445a827999fcef3fea9b66290ea1b54044471cece6b9a33fed906bcf328d53404430fbc54d5d523fef6297cff75cb7404418f8b83c69a53ff00000000000004044000000000000'), 4326)),("test"."Geometry") )) 


  		




  
  27
  
    
ORDER BY ST_Distance(ST_GeomFromWKB((X'000000000300000001000000213ff000000000000040440000000000003fef6297cff75cb04043e70747c3965a3fed906bcf328d464043cf043ab2a2ad3fea9b66290ea1a34043b8e31319465d3fe6a09e667f3bcd4043a57d866603113fe1c73b39ae68c940439592675bc5793fd87de2a6aea964404389be50c335cb3fc8f8b83c69a60d40438275a0c0228d3c91a62633145c074043800000000000bfc8f8b83c69a60840438275a0c0228dbfd87de2a6aea962404389be50c335cbbfe1c73b39ae68c640439592675bc579bfe6a09e667f3bcc4043a57d86660311bfea9b66290ea1a44043b8e31319465dbfed906bcf328d474043cf043ab2a2adbfef6297cff75cb14043e70747c3965abff00000000000004044000000000000bfef6297cff75cae404418f8b83c69a6bfed906bcf328d42404430fbc54d5d53bfea9b66290ea19c4044471cece6b9a3bfe6a09e667f3bc240445a827999fcefbfe1c73b39ae68bb40446a6d98a43a87bfd87de2a6aea94140447641af3cca35bfc8f8b83c69a5bb40447d8a5f3fdd733ce6586c6b36175f40448000000000003fc8f8b83c69a66a40447d8a5f3fdd733fd87de2a6aea99340447641af3cca353fe1c73b39ae68e040446a6d98a43a863fe6a09e667f3be240445a827999fcef3fea9b66290ea1b54044471cece6b9a33fed906bcf328d53404430fbc54d5d523fef6297cff75cb7404418f8b83c69a53ff00000000000004044000000000000'), 4326), "Geometry") ASC NULLS LAST, 


  		




  
  28
  
    
    "test"."ID" ASC 


  		




  
  29
  
    
LIMIT 1;


  		












Also available in:  Unified diff