Skip to content

Instantly share code, notes, and snippets.

@mexitek
Created August 25, 2011 19:12
Show Gist options
  • Save mexitek/1171529 to your computer and use it in GitHub Desktop.
Save mexitek/1171529 to your computer and use it in GitHub Desktop.
Script to automate an import process from .json file to postgre DB.
<?php
# Configure
$DB_USER = 'admin';
$DB_PASS = 'qwerty';
$DB_HOST = 'localhost';
$DB_NAME = 'postgis-2-0';
// Param 3 when ran in shell will override this value
$DB_TABLE = 'public.florida_establishments_garman';
// Param 2 when ran in shell will override this value
$CATEGORY = 'misc';
// ===============================
// = Do NOT Edit Below This Line =
// ===============================
# Params
$CURRENT_SCRIPT = $argv[0];
$JSON_FILE = $argv[1];
$CATEGORY = isset($argv[2]) ? $argv[2]:$CATEGORY;
$POSTGRE_TABLE = isset($argv[3]) ? $argv[3]:$DB_TABLE;
# Open our file
$f = file_get_contents($JSON_FILE);
# Make sure file is good
if( $f === FALSE )
{
die('Could not open file.');
}
# Open Postgre Connections
else
{
$dbconn = pg_connect("host=$DB_HOST dbname=$DB_NAME user=$DB_USER password=$DB_PASS")
or die('Could not connect: ' . pg_last_error());
}
# Decode JSON into array
$points = json_decode( $f, true );
# Iterate our points
foreach($points as $p)
{
/*
$p['Latitude']
$p['Longitude']
$p['Establishment']
$p['Address_Phone']
*/
# Sanatize
$est = str_replace("'","\'",$p['Establishment']);
$add = str_replace("'","\'",$p['Address_Phone']);
# Insert into DB
pg_query($dbconn,"INSERT INTO $POSTGRE_TABLE (establishment,address_phone,category,location) VALUES ( '".$est."', '".$add."', '".$CATEGORY."',ST_Makepoint(".$p['Latitude'].",".$p['Longitude'].") )") or die("Could not execute this insert statement: ".pg_last_error());
}
?>
@mexitek
Copy link
Author

mexitek commented Aug 26, 2011

How to use

bash ~$ php json_to_postgre.php path_to_json_file

Category Param

Used for category column in data table.

bash ~$ php json_to_postgre.php path_to_json_file attractions

Table Param

Used to specify which table the data gets stored into.

bash ~$ php json_to_postgre.php path_to_json_file attractions florida_attractions

@JRMC10
Copy link

JRMC10 commented Dec 13, 2017

hola buenas tardes disculpa hice lo mismo que en tu codigo pero ahora necesito que al guardar varios datos mas en el json se inserten en la base de datos pero me toma el primer valor y no me deja introducirlo como le hago para comparar o que ya no lea los que ya están insertados.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment