Category

Databases

How to Import Your Rails Data Into Salesforce With CSV

By DatabasesOne Comment

Oftentimes, companies want to move resources into Salesforce. It only makes sense, being that salesforce is one of the top Customer Relationship Managers (CRMs) available right now. There are loads of benefits to storing data in Salesforce, including security, organization, and data recovery. What is the process of moving data to Salesforce like? Let’s talk about CSVs.

Process

 

  • When working in Rails, all your data is stored in a database. First you’ll need to pull the required data from the database and put it inside a CSV file, using the psql command line. CSV stands for comma-separated values, and is a way of storing table-like data in a text format.

user$ psql
user=# \c database
database=# \COPY my_table TO 'filename.csv' CSV HEADER

This will create a CSV file in the chosen directory with the contents of the table from your database.

  • From here, we will begin to upload our CSV file to Salesforce. First you’ll need to go to SETUP > DATA > DATA IMPORT WIZARD. Where you can find and launch the wizard to import data.
  • Once the wizard is launched you will be asked to choose what type of data you are importing. This is basically asking which Salesforce object you are looking to import, whether it be contacts, leads, or a custom object you created. Select which type you are planning to import.

  • Next you will be asked what you want to do. Since we’re importing new data in this tutorial, we will select “Add new records”

  • After that, select the CSV file you would like to import, select the correct Character Code, and locate the option by which the values are separated by. The character code and separator, will more often than not be the default values Salesforce sets. After this step, then just press “Next” at the bottom right of your screen.

  • After pressing “Next”, you will be directed to a new page asking to assign Salesforce objects to the CSV headers.

Some headers will be mapped already, as Salesforce typically knows what some values will be mapped to, saving you time. However in this situation, our contact_name and acctount_name headers are not mapped, so we will have to do it manually, starting by clicking “map” on the left side of the screen.

  • Mapping an object to a header is quite simple, as all you need to do from the pop-up box, is find the related field, or fields, and select them to map.

Now after repeating that for each un-mapped header, you are ready to begin importing.

  • Click the “Next” button at the bottom right corner of the screen for a review of what is being imported. From there, just press “Start Import” to begin putting your data into Salesforce. Note: This can take some time depending on how much data you have.
  • After the import you should now have a list of objects available to you on your Salesforce homepage.

Conclusion

 

Now that you know how to import your data, you have the power of Salesforce on your side. You can create graphs, organize your data, as well as many other ways to improve your business. Data can also be updated and new data can be added at any time, so you can always have Salesforce up to date with the rest of your company. If you are looking to centralize your data in either Salesforce or Rails, rather than both, look here to help you make a decision.

GIS Crawling Done Easy Using Python With PostGreSQL

By AWS, Databases, Development, GIS, Python, RDSNo Comments

Problem

Company “Help-i-copter” allows users to rent a ride in a helicopter for a lift in style. However, Help-i-copter is having trouble creating software that find the best flat surface for the helicopter pilots to land to pick up their customers. A perfect solution would be crawling data from sites and to store it on a PostGreSQL database. What steps should they take?

Crawling

Crawling, in software terms, is the act of copying data from a website using a computer program. This data can be saved to a file, printed to your screen, or put into a database. It entirely depends on the project and its scale. On a much greater scale, where data can be overwhelming, a database would be the best option, which is why it’s important to have one. You could have your grocery list stored online, but you don’t want to login every time to see it. You could instead crawl that information off the web with a command like:

soup.find_all('a', attrs={'class': ‘list'})

  • Write a crawling program that collects data from multiple sources, to get the most accurate data. This program would most likely be written in Python as it has access to modules like Requests and BeautifulSoup.

import requests
from bs4 import BeautifulSoup
url = "http://www.placeforyourhelicopter.com"
r = requests.get(url)
soup = BeautifulSoup(r.content, "html.parser")
surfaces = soup.find_all('a', attrs={'class': 'surface'})
for d in surfaces:
#getting all the surface information

  • Store the found data on a PostGreSQL database. This allows the data to be stored and sorted. When it’s sorted, this allows the data to be queried quickly and efficiently.

import psycopg2
endpoint = "surfaces.amazonaws.com"
c = psycopg2.connect(host=endpoint ,database="surfaces", user="admin", password="admin")
cursor = c.cursor()
query = "INSERT INTO surfaces (airtraffic,windspeed,date_produced,lat,lon,state) VALUES (%s,%s,%s,%s,%s,%s);"
data = #all info from surfaces in an array
cursor.execute(query, data)

  • Create an app that allows pilots to enter in a variable they want to query and parameters for that variable. Then use Python and the Psycopg2 module to query the data accordingly. This app with allow non-programmers to access the database without having to learn PostGreSQL.

letter = raw_input("Choose a number: ")
cursor.execute("SELECT * FROM surfaces WHERE airtraffic LIKE " + letter)
for row in cursor:
print str(row) + "\n"

 

Databases

So why is it important to store large amounts of data on a database? Simply, it gives you an easy way to find and query data. For example, let’s say you have a list of employees you got from your company’s website, and added it to a PostGreSQL database. In such a database, finding data like “all employees with a first name that begins with an L” would be much simpler, as databases are well organized. Simple commands like:

where employee_name LIKE 'L%'

would return “Larry, Liz, and Lucy” quickly and efficiently.

Bounding Box

 

This data could be used in a lot of ways. Namely, we could use out latitude and longitude coordinates to create a bounding box and get information about the areas within that box. This could help Help-i-copter in a number of ways. Pilots could use a bounding box to find flat areas near them and sort those by air-traffic, location, etc… It will be essentially asking the pilot the maximum and minimum coordinates of the box and then looking through the database for any surface that fit the description. Here’s what that might look like in Python:

xmin = raw_input("What is the minimum latitude? ")
xmax = raw_input("What is the maximum latitude? ")
ymin = raw_input("What is the minimum longitude? ")
ymax = raw_input("What is the maximum longitude? ")
cursor.execute("SELECT * FROM surfaces WHERE GEOMETRY(lat,lon) && ST_MakeEnvelope("
+ ymin + "," + ymin + "," + xmax + "," + ymax + ", 4326)")
for row in cursor:
print str(row) + "\n"

Conclusion

 

As you can see, crawling and databases can work very well together, especially when crawling large amounts of data. It would be otherwise inefficient and a lot slower to just store data on a normal document, or to do a crawl of the page every time you run the program. Help-i-copter able to efficiently crawl a site, upload data to a web page, and query that data back to a pilot quickly. Thanks to the power of PostGreSQL and Python.

Enabling PostGIS on PostgreSQL with Amazon Web Services(AWS) | Relational Database Service(RDS)

By AWS, Databases, GIS

Part 1 of our PostGIS series is to help developers enable PostGIS for a PostgreSQL database in AWS.

Amazon’s Relational Database Service (RDS) allows users to launch PostgreSQL databases easily and quickly. PostgreSQL allows developers to extend the functionality of the core database via extensions.  For this reason, extensions loaded into the database can function just like features that are built in.

Because this database is on Amazon’s servers, they have limitations on what extensions the will allow to be hosted on their service.  These limitations are mostly for performance reasons.

PostGIS Version

By default, the database has PostGIS installed on it. This isn’t critical to run, but it may be helpful in your troubleshooting efforts.

To get the current version, use this:

SELECT PostGIS_full_version();

Result:

POSTGIS=”2.3.2 r15302″ GEOS=”3.5.1-CAPI-1.9.1 r4246″ PROJ=”Rel. 4.9.3, 15 August 2016″ GDAL=”GDAL 2.1.3, released 2017/20/01″ LIBXML=”2.9.1″ LIBJSON=”0.12″ RASTER

Normal Installation Guidance

If you follow PostGIS’s guidance here, you will get the following error.

When you run this command:
CREATE EXTENSION postgis_sfcgal;

ERROR: Extension “postgis_sfcgal” is not supported by Amazon RDS
DETAIL: Installing the extension “postgis_sfcgal” failed, because it is not on the list of extensions supported by Amazon RDS.
HINT: Amazon RDS allows users with rds_superuser role to install supported extensions. See: SHOW rds.extensions;

AWS RDS PosGIS Installation Guidance

AWS covers enabling PosGIS via their common database administrator (DBA) tasks here.

In about 3 minutes, you can run through these commands and enable PosGIS tables in your database.

AWS Supported Extensions

This is a pretty nifty command to see what extensions are displayed on the device.

SHOW rds.extensions;

Here is a list of the Posgresql Extentions that RDS does support:

address_standardizer
address_standardizer_data_us
bloom
btree_gin
btree_gist
chkpass
citext
cube
dblink
dict_int
dict_xsyn
earthdistance
fuzzystrmatch
hll
hstore

hstore_plperl
intagg
intarray

ip4r
isn
log_fdw
ltree
pgaudit

pgcrypto
pgrouting
pgrowlocks
pgstattuple
pg_buffercache
pg_freespacemap
pg_hint_plan
pg_prewarm
pg_repack
pg_stat_statements
pg_trgm
pg_visibility
plcoffee

plls
plperl
plpgsql

pltcl
plv8
postgis
postgis_tiger_geocoder
postgis_topology
postgres_fdw
sslinfo
tablefunc
test_parser
tsearch2
tsm_system_rows
tsm_system_time
unaccent
uuid-ossp