<commit_before>#!/usr/bin/env python
# -*- coding: utf-8 -*-

import psycopg2
import json
from geojson import loads, Feature, FeatureCollection

db_host = "localhost"
db_user = "postgres"
db_passwd = "<PASSWORD>"  # secret
db_database = "py_geoan_cb"
db_port = "5432"

# connect to DB
conn = psycopg2.connect(host=db_host, user=db_user, port=db_port,
                        password=<PASSWORD>, database=db_database)

# create a cursor
cur = conn.cursor()

# add columns needed for pgRouting to our imported network lines
add_source = '''ALTER TABLE geodata.ch08_e01_networklines ADD COLUMN source INTEGER;'''
add_target = '''ALTER TABLE geodata.ch08_e01_networklines ADD COLUMN target INTEGER;'''
add_cost = '''ALTER TABLE geodata.ch08_e01_networklines ADD COLUMN cost DOUBLE PRECISION;'''

cur.execute(add_source)
cur.execute(add_target)
cur.execute(add_cost)

# passing the name of the imported table
# plus the tolerance value, geometry column and primary key field
create_topo_table = '''
    SELECT public.pgr_createTopology('geodata.ch08_e01_networklines',
        0.0001, 'wkb_geometry', 'ogc_fid');
    '''
# run the create table query
cur.execute(create_topo_table)

# commit the new table to the database
conn.commit()

# start_coord = "71384.9532168 164571.903749"
# end_coord = "71398.8429459 164493.503944"

# find the start node id within 1 meter of the given coordinate
# used as input in routing query start point
start_node_query = """
    SELECT id FROM geodata.ch08_e01_networklines_vertices_pgr AS p
    WHERE ST_DWithin(the_geom, ST_GeomFromText('POINT(71384.9532168 164571.903749)',31255), 1);"""

# locate the end node id within 1 meter of the given coordinate
end_node_query = """
    SELECT id FROM geodata.ch08_e01_networklines_vertices_pgr AS p
    WHERE ST_DWithin(the_geom, ST_GeomFromText('POINT(71398.8429459 164493.503944)',31255), 1);
    """

# get the start node id as an integer
cur.execute(start_node_query)
sn = int(cur.fetchone()[0])

# get the end node id as an integer
cur.execute(end_node_query)
en = int(cur.fetchone()[0])


# pgRouting query to return our list of segments representing
# our shortest path Dijkstra results as GeoJSON
# query returns the shortes path between our start and end nodes above<commit_msg>remove setup SQL alter table queries moved to getting setup section<commit_after>#!/usr/bin/env python
# -*- coding: utf-8 -*-

import psycopg2
import json
from geojson import loads, Feature, FeatureCollection

db_host = "localhost"
db_user = "postgres"
db_passwd = "<PASSWORD>"
db_database = "py_geoan_cb"
db_port = "5432"

# connect to DB
conn = psycopg2.connect(host=db_host, user=db_user, port=db_port,
                        password=<PASSWORD>, database=db_database)

# create a cursor
cur = conn.cursor()

# start_coord = "71384.9532168 164571.903749"
# end_coord = "71398.8429459 164493.503944"

# find the start node id within 1 meter of the given coordinate
# used as input in routing query start point
start_node_query = """
    SELECT id FROM geodata.ch08_e01_networklines_vertices_pgr AS p
    WHERE ST_DWithin(the_geom, ST_GeomFromText('POINT(71384.9532168 164571.903749)',31255), 1);"""

# locate the end node id within 1 meter of the given coordinate
end_node_query = """
    SELECT id FROM geodata.ch08_e01_networklines_vertices_pgr AS p
    WHERE ST_DWithin(the_geom, ST_GeomFromText('POINT(71398.8429459 164493.503944)',31255), 1);
    """

# get the start node id as an integer
cur.execute(start_node_query)
sn = int(cur.fetchone()[0])

# get the end node id as an integer
cur.execute(end_node_query)
en = int(cur.fetchone()[0])


# pgRouting query to return our list of segments representing
# our shortest path Dijkstra results as GeoJSON
# query returns the shortes path between our start and end nodes above
{'fraction_non_alphanumeric': 0.0940959409594096, 'fraction_numerical': 0.07248286768581971, 'mean_word_length': 4.8474576271186445, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7309739', 'n_tokens_mistral': 1440, 'n_tokens_neox': 1254, 'n_words': 385}