Newer
Older
osmCoverage / src / t_BUSSTOP.sql
@hayashi hayashi on 1 Oct 2017 1 KB ToPostgis
  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. -------------------------
  7. CREATE TABLE t_busstop (
  8. gid SERIAL PRIMARY KEY,
  9. gmlid text,
  10. name text,
  11. fixed integer,
  12. area integer,
  13. geom GEOMETRY(POINT, 4612)
  14. );
  15. CREATE INDEX ix_busstop_geom ON t_busstop USING GiST (geom);
  16.  
  17. insert into t_busstop(osm_id,name,geom) select osm_id, st_transform(way,4612) FROM planet_osm_point WHERE (highway='bus_stop');
  18. insert into t_busstop(osm_id,name,geom) VALUES(884, 'なまえ', ST_GeomFromText('POINT(139.4110836 35.4341053)', 4612));
  19.  
  20. ----
  21. CREATE TABLE t_test (
  22. gid SERIAL PRIMARY KEY,
  23. osm_id bigint,
  24. name text,
  25. geom GEOMETRY(POINT, 4612)
  26. );
  27.  
  28. CREATE INDEX ix_test_geom ON t_test USING GiST (geom);
  29.  
  30. insert into t_test(osm_id,name,geom) VALUES(884, 'なまえ', ST_GeomFromText('POINT(139.4110836 35.4341053)', 4612));
  31.  
  32. ----
  33.  
  34. ---<< database: hsqldb >>---
  35. CREATE TABLE bus_stop (
  36. gmlid VARCHAR(12) NOT NULL,
  37. name VARCHAR(128),
  38. lat DOUBLE,
  39. lon DOUBLE,
  40. fixed INT,
  41. fixed1 INT,
  42. area INT,
  43. ifile VARCHAR(128),
  44. up INT,
  45. CONSTRAINT bus_stop_pk PRIMARY KEY(gmlid,area));
  46. CREATE INDEX bus_stop_index ON bus_stop(lat,lon);
  47. SELECT gmlid,name,lat,lon,fixed1,area FROM bus_stop;
  48.  
  49. CREATE TABLE OSM_EXIST (
  50. osmid VARCHAR(12) NOT NULL,
  51. name VARCHAR(128),
  52. lat DOUBLE,
  53. lon DOUBLE,
  54. score INT,
  55. CONSTRAINT existing_pk PRIMARY KEY(osmid, lat, lon));
  56.  
  57. CREATE TABLE coverage (
  58. area INT,
  59. name VARCHAR(128),
  60. denominator BIGINT,
  61. lv1 BIGINT,
  62. lv2 BIGINT,
  63. lv3 BIGINT);
  64.  
  65. -------------------------