Skip to content

Instantly share code, notes, and snippets.

@andrewxhill
Created October 10, 2012 22:01
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save andrewxhill/3868758 to your computer and use it in GitHub Desktop.
New SQL statement for bigline generation
WITH line AS (SELECT ST_GeometryFromText('LINESTRING( -112.445 42.87133, -112.445 42.87124, -112.444 42.87086, -112.444 42.87086, -112.445 42.87055, -112.445 42.8705000, -112.446 42.8702000, -112.446 42.86988, -112.447 42.8695500, -112.447 42.8695500, -112.447 42.8703200, -112.448 42.8710600, -112.449 42.87183, -112.449 42.87203, -112.449 42.8722000, -112.450 42.8723100, -112.450 42.87268, -112.450 42.87279, -112.450 42.87279, -112.450 42.87326, -112.450 42.87404, -112.450 42.8744600, -112.450 42.87505, -112.450 42.87518, -112.450 42.8767600, -112.450 42.8768, -112.450 42.8769000, -112.450 42.8769000, -112.450 42.8768700, -112.450 42.8768700, -112.450 42.87686, -112.451 42.8768300, -112.451 42.87682, -112.451 42.8768, -112.453 42.87664, -112.453 42.8766500, -112.455 42.8766700, -112.456 42.87666, -112.456 42.8766300, -112.456 42.8766100, -112.456 42.8765200, -112.457 42.87615, -112.457 42.87615, -112.457 42.8760900, -112.457 42.87608, -112.457 42.8761100, -112.458 42.87635, -112.459 42.8773000, -112.460 42.87865, -112.461 42.8792200, -112.461 42.87926, -112.462 42.87991, -112.463 42.8802800, -112.463 42.8805000, -112.464 42.88127, -112.465 42.8820200, -112.465 42.88208, -112.465 42.8824900, -112.466 42.8831200, -112.466 42.8834100, -112.466 42.88349, -112.468 42.8848700, -112.470 42.88595, -112.470 42.88624, -112.472 42.88776, -112.474 42.88917, -112.474 42.88926, -112.474 42.8892800, -112.474 42.8897700, -112.475 42.89029, -112.475 42.89068, -112.476 42.89077, -112.477 42.89155, -112.477 42.8916900, -112.477 42.8920700, -112.478 42.8925400, -112.478 42.89273, -112.479 42.8933700, -112.480 42.8939, -112.480 42.89412, -112.484 42.8956, -112.484 42.89574, -112.485 42.8959300, -112.485 42.89618, -112.486 42.89658, -112.487 42.8968700, -112.488 42.8972000, -112.489 42.8975400, -112.490 42.89802, -112.493 42.8994200, -112.495 42.90023, -112.495 42.9002700, -112.496 42.9003400, -112.496 42.9004900, -112.497 42.90095, -112.498 42.9011800, -112.500 42.90214, -112.500 42.9021500, -112.501 42.90252, -112.501 42.9026000, -112.502 42.90306, -112.503 42.9033100, -112.503 42.90337, -112.503 42.90352, -112.503 42.9035800, -112.504 42.90375, -112.505 42.9041600, -112.507 42.9051500, -112.508 42.90552, -112.509 42.9058000, -112.510 42.9063600, -112.511 42.9068300, -112.513 42.9073600, -112.513 42.90755, -112.514 42.9078, -112.514 42.9079000, -112.514 42.90796, -112.515 42.9081500, -112.515 42.9083700, -112.516 42.90852, -112.517 42.90885, -112.518 42.90901, -112.519 42.9093300, -112.520 42.90959, -112.521 42.90975, -112.521 42.9097800, -112.522 42.90991, -112.523 42.9103200, -112.525 42.9107000, -112.525 42.9108, -112.526 42.9108600, -112.526 42.9109000, -112.526 42.9109500, -112.527 42.9109700, -112.527 42.91111, -112.527 42.91125, -112.528 42.9113900, -112.528 42.9117100, -112.528 42.91181, -112.528 42.91199, -112.528 42.91237, -112.529 42.9124400, -112.529 42.91266, -112.529 42.9129, -112.529 42.9132500, -112.529 42.9132500, -112.530 42.91317, -112.530 42.9130500, -112.531 42.9129200, -112.532 42.91284, -112.532 42.91279, -112.533 42.91273, -112.534 42.9126700, -112.535 42.9125600, -112.535 42.91255, -112.538 42.9123600, -112.539 42.9122000, -112.540 42.9121300, -112.544 42.9115500, -112.546 42.9110100, -112.549 42.91033, -112.555 42.9084, -112.567 42.9048800, -112.572 42.9034000, -112.576 42.902, -112.580 42.901, -112.581 42.9007600, -112.583 42.90003, -112.584 42.8998900, -112.584 42.8998700, -112.584 42.8998700, -112.584 42.8998700, -112.584 42.8998600, -112.584 42.8998000, -112.584 42.8996900, -112.585 42.8995100, -112.585 42.8994600, -112.585 42.89934, -112.586 42.8990800, -112.587 42.89873, -112.588 42.89815, -112.589 42.8979600, -112.590 42.8976000, -112.591 42.8973300, -112.592 42.8971100, -112.592 42.8969300, -112.594 42.89658, -112.598 42.89551, -112.602 42.8947400, -112.606 42.89368, -112.608 42.89329, -112.613 42.89204, -112.615 42.89173, -112.616 42.8915200, -112.619 42.89068, -112.621 42.89023, -112.623 42.8898600, -112.624 42.8896600, -112.625 42.88951, -112.626 42.88942, -112.627 42.88936, -112.628 42.8892800, -112.629 42.88927, -112.630 42.88918, -112.631 42.8891400, -112.632 42.8891000, -112.633 42.88904, -112.633 42.8890100, -112.633 42.88895, -112.633 42.88893, -112.634 42.88884, -112.634 42.8887800, -112.634 42.88871, -112.635 42.88855, -112.636 42.88823, -112.638 42.8877, -112.638 42.8874800, -112.639 42.8873700, -112.640 42.8868100, -112.642 42.88635, -112.643 42.8861000, -112.644 42.88573, -112.647 42.88454, -112.648 42.88448, -112.650 42.8836600, -112.654 42.8825800, -112.660 42.88076, -112.663 42.87984, -112.664 42.8794900, -112.666 42.87906, -112.667 42.87866, -112.668 42.87827, -112.669 42.8780200, -112.671 42.87749, -112.677 42.8759100, -112.678 42.87541, -112.679 42.87519, -112.680 42.8749900, -112.681 42.87489, -112.682 42.87476, -112.682 42.87467, -112.684 42.87456, -112.685 42.8744800, -112.686 42.8744300, -112.687 42.8743500, -112.688 42.8743400, -112.689 42.8742, -112.690 42.87413, -112.691 42.874, -112.692 42.8738100, -112.693 42.87366, -112.694 42.8733800, -112.695 42.8732300, -112.695 42.8731100, -112.695 42.8731100, -112.696 42.87292, -112.696 42.8728200, -112.697 42.87259, -112.697 42.8722900, -112.699 42.87185, -112.699 42.8716100, -112.700 42.8713500, -112.700 42.8708800, -112.701 42.8704500, -112.704 42.8686, -112.705 42.8681900, -112.706 42.8674500, -112.708 42.8663800, -112.709 42.86569, -112.710 42.8651900, -112.712 42.86455, -112.713 42.8639800, -112.717 42.86285, -112.718 42.86243, -112.719 42.86243, -112.919 42.85843)',4326) as geom),
buff AS (SELECT ST_Union(ST_Buffer(geom::geography,50)::geometry) as geom from line)
SELECT usa_admin.the_geom_webmercator FROM usa_admin,buff WHERE ST_Intersects(the_geom,geom)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment