Newer
Older
osmCoverage / src / t_FUEL.sql
  1. ---<< postgis: postgres >>---
  2. db_driver=org.postgresql.Driver
  3. db_url=jdbc:postgresql://surveyor.mydns.jp:5432/gisdb
  4. db_user=gisuser
  5. db_passwd=gisuser
  6. ---<< osmdb: postgres >>---
  7. db_driver=org.postgresql.Driver
  8. db_url=jdbc:postgresql://localhost:5432/osmdb
  9. db_user=postgres
  10. db_passwd=*******
  11. -------------------------
  12. DROP TABLE t_FUEL;
  13. CREATE TABLE t_FUEL (
  14. gid SERIAL PRIMARY KEY,
  15. idref text,
  16. gmlid text,
  17. area integer,
  18. fixed integer,
  19. geom GEOMETRY(POINT, 4612)
  20. );
  21. CREATE INDEX ix_t_FUEL_geom ON t_fuel USING GiST (geom);
  22. DELETE FROM t_FUEL;
  23. insert into t_FUEL (gmlid,fixed,area,geom) VALUES('n000001',0,0, ST_GeomFromText('POINT(136.9695284611471 35.10300377075564)', 4612));
  24. insert into t_FUEL (gmlid,fixed,area,geom) VALUES('n000002',0,0, ST_GeomFromText('POINT(136.969047121627 35.102834616747685)', 4612));
  25. insert into t_FUEL (gmlid,fixed,area,geom) VALUES('n000003',0,0, ST_GeomFromText('POINT(136.9743151095072 35.10462957171922)', 4612));
  26. -------------------------
  27. GRANT SELECT ON t_fuel TO gisuser;
  28.  
  29. ---<< database: hsqldb >>---
  30. -------------------------
  31.  
  32. CREATE TABLE FUEL_EXIST (
  33. idref VARCHAR(12) NOT NULL,
  34. lat DOUBLE,
  35. lon DOUBLE,
  36. score INT,
  37. PRIMARY KEY(idref)
  38. );
  39. CREATE INDEX FUEL_EXIST_index ON FUEL_EXIST (lat, lon);
  40.  
  41. ----
  42. CREATE TABLE FUEL (
  43. gid INT,
  44. idref VARCHAR(12) NOT NULL,
  45. lat DOUBLE,
  46. lon DOUBLE,
  47. fixed INT,
  48. area INT,
  49. up INT,
  50. fixed1 INT
  51. )
  52.  
  53. // new data
  54. INSERT INTO FUEL (gid,lat,lon,fixed,idref,area,up,fixed1) VALUES(0,?,?,0,?,?,2,0)
  55.  
  56. // read PostGIS
  57. INSERT INTO FUEL (gid,lat,lon,fixed,idref,area,up,fixed1) VALUES(?,?,?,?,?,?,0,0)
  58.  
  59. ----
  60. CREATE TABLE AREA_NODE (
  61. idref VARCHAR(12) NOT NULL,
  62. pid VARCHAR(12),
  63. lat DOUBLE,
  64. lon DOUBLE
  65. )
  66.  
  67. ----